February 6, 2009 at 1:42 am
Hi All,
I am trying to generate a SQL Reporting service for one of the functionality. The data in my table looks something like this:
ID Response
---------------------------------------------
1 Ques1=1&Ques2=6&Ques3=4&Ques4=5
2 Ques1=2&Ques2=2&Ques3=4&Ques5=5
3 Ques1=4&Ques2=4&Ques3=4&Ques4=5
4 Ques1=3&Ques2=5&Ques3=4&Ques6=5
The string in the response column is not fixed and will change based upon some conditions in the application. Ideally I want to parse the string into individual columns and then display that in SQL Reporting services.
Output:
ID Ques1 Ques2 Ques3 Ques4 Ques5 Ques6
------------------------------------------------------------
1 1 6 4 5 - -
2 2 2 4 - 5 -
3 4 4 4 5 - -
4 3 5 4 - - 5
I am wrtiting a Stored Procedure to do that.
Any help is appreciated.
Thanks,
Naveen
February 6, 2009 at 6:55 am
Hi
Firstly, I'd recommend splitting up the table if possible!
If that's not possible, is each question's score always only one character? If so, I would use the SUBSTRING function like this:
SELECT ID, SUBSTRING(Response, 7,1) as Ques1, SUBSTRING(Response, 15,1) as Ques2 etc.
If the number of characters used in the score for each question could vary, I think you'd need to use the CHARINDEX function to find the position of "Ques1=", "&Ques2=" etc. then calculate the position of the scores from there.
A
February 6, 2009 at 7:00 am
Check out this article, http://qa.sqlservercentral.com/articles/TSQL/62867/ as it addresses this issue.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 6, 2009 at 7:13 am
[font="Verdana"]PIVOT operator will help in this case. For more information refer BOL
Mahesh[/font]
MH-09-AM-8694
February 6, 2009 at 8:22 am
Hi
This is your Table:
ID Response
---------------------------------------------
1 Ques1=1&Ques2=6&Ques3=4&Ques4=5
2 Ques1=2&Ques2=2&Ques3=4&Ques5=5
3 Ques1=4&Ques2=4&Ques3=4&Ques4=5
4 Ques1=3&Ques2=5&Ques3=4&Ques6=5
You need to convert this into i.e. 3 columns (ID, Question, Response). After this you can use PIVOT to convert this into Columns.
ID Question Response
1 Ques1 1
1 Ques2 6
1 Ques3 4
..........................
2 Ques1 2
2 Ques2 2
2 Ques3 4
.........................
PIVOT is very easy. You can check out the BOL. but to convert above structure into below structure is quite challenging.
Check out the link:http://decipherinfosys.wordpress.com/2007/05/24/simulating-a-pipeline-function-in-sql-server/
Use logic and convert the string into Rows and Columns (i..e Above format ) and then apply PIVOT.
Thanks -- Vj
February 6, 2009 at 8:28 am
[font="Verdana"] simple example for PIVOT:
CREATE TABLE #temp123
(
Country varchar(15),
Variable varchar(20),
VaribleValue int
)
Go
INSERT INTO #temp123 VALUES ('North America','Sales',2000000)
INSERT INTO #temp123 VALUES ('North America','Expenses',1250000)
INSERT INTO #temp123 VALUES ('North America','Taxes',250000)
INSERT INTO #temp123 VALUES ('North America','Profit',500000)
INSERT INTO #temp123 VALUES ('Europe','Sales',2500000)
INSERT INTO #temp123 VALUES ('Europe','Expenses',1250000)
INSERT INTO #temp123 VALUES ('Europe','Taxes',500000)
INSERT INTO #temp123 VALUES ('Europe','Profit',750000)
INSERT INTO #temp123 VALUES ('South America','Sales',500000)
INSERT INTO #temp123 VALUES ('South America','Expenses',250000)
INSERT INTO #temp123 VALUES ('Asia','Sales',800000)
INSERT INTO #temp123 VALUES ('Asia','Expenses',350000)
INSERT INTO #temp123 VALUES ('Asia','Taxes',100000)
Go
/** Show original table **/
SELECT * FROM #temp123
Go
/** Create crosstab using PIVOT **/
SELECT *
FROM #temp123
PIVOT
(
SUM(VaribleValue)
FOR [Variable]
IN ([Sales],[Expenses],[Taxes],[Profit])
)
AS p
Go
DROP TABLE #temp123
Go
Mahesh
[/font]
MH-09-AM-8694
February 6, 2009 at 8:37 am
HI Mahesh,
You have provided partial solution, i.e. converting rows into Columns. But his requirement is slightly different. i.e Convert a Column which contains concatenated string into multiple columns. In this case we can't directly use PIVOT.
Thanks
Vijaya Kadiyala
February 9, 2009 at 1:30 am
[font="Verdana"]
Vijaya Kadiyala (2/6/2009)
HI Mahesh,You have provided partial solution, i.e. converting rows into Columns. But his requirement is slightly different. i.e Convert a Column which contains concatenated string into multiple columns. In this case we can't directly use PIVOT.
Thanks
Vijaya Kadiyala
Your are absolutely right Vijaya. I tried to give some hint to the poster so that poster can find out solution.
Mahesh
[/font]
MH-09-AM-8694
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply