question

Karthik Venkatraman avatar image
Karthik Venkatraman asked

Display comma seperated values in a single column as individual rows

Hi, I am working on a application and i need to output some comma seperated values stored in a single column to a seperate rows or columns. The below is the original output of the Selectc Query. ![alt text][1] In the desired output the comma seperated values of Item_Type ie R,U must be displayed in seperate rows and the appropriate Item_Qty for R and U must be displayed in the next column. The desired output must look alike the below one ![alt text][2] I dont know how to code this one as I am a beginner of sql. I will be having only select access in the database and so i cannot run any cursors, views and other things to perform this. Any help regarding this is very much appreciated. Thanks in advance... Regards, Karthik [1]: /storage/temp/299-raw.jpg [2]: /storage/temp/300-outout.jpg
sql-server-2008querypivotquery-resultscsv
raw.jpg (45.8 KiB)
outout.jpg (24.5 KiB)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Karthik Venkatraman Please post the data in a format which can be copied OR best would be to post the DDLs of the table, DATA-INSERTION script and the desired output. This way you have more chances getting a swift response.
0 Likes 0 ·
Usman Butt avatar image
Usman Butt answered
OK, I guess this is one way of doing it. First, I pivoted the data to get the ROWS into COLUMNS, then Split the comma delimited data into rows using CHARINDEX (Since you mention only 1 comma this was easy, but if the delimited values are more than four or so then you may have to use the cteTally) DECLARE @table TABLE ( UID INT ,VUID INT ,FieldName VARCHAR(50) ,FieldValue VARCHAR(50) ) INSERT @table ( [UID] ,[VUID] ,[FieldName] ,[FieldValue] ) SELECT 1295 , 161 , 'CARBO' , '2' UNION ALL SELECT 1296 , 161 , 'CARBO_NEEDED' , '2' UNION ALL SELECT 1298 , 161 , 'item_Qty' , '4,2' UNION ALL SELECT 1299 , 161 , 'item_type' , 'R,U' UNION ALL SELECT 1410 , 174 , 'CARBO' , '2' UNION ALL SELECT 1411 , 174 , 'CARBO_NEEDED' , '1' UNION ALL SELECT 1413 , 174 , 'item_Qty' , '4,1' UNION ALL SELECT 1414 , 174 , 'item_type' , 'R,U' UNION ALL SELECT 1415 , 176 , 'CARBO' , '2' UNION ALL SELECT 1416 , 176 , 'CARBO_NEEDED' , '2' UNION ALL SELECT 1417 , 176 , 'item_Qty' , '3,2' UNION ALL SELECT 1418 , 176 , 'item_type' , 'R,U' ; /*==== PIVOT THE ROWS INTO COLUMNS =================================*/ WITH PIVOTEDDATA AS ( SELECT [VUID] , [item_Qty] , [item_type] , [CARBO] , [CARBO_NEEDED] FROM ( SELECT [VUID] , [FieldName] , [FieldValue] FROM @table AS T ) SplitOutput PIVOT ( MAX([FieldValue]) FOR [FieldName] IN ( [item_Qty], [item_type], [CARBO], [CARBO_NEEDED] ) ) Pivoted ) /*======== CROSS APPLY TO GET THE SPLITTED DATA INTO COLUMNS =================================*/ SELECT [VUID] , Splitter.[item_type] , Splitter.[item_Qty] , [CARBO] , [CARBO_NEEDED] FROM PIVOTEDDATA CROSS APPLY ( SELECT /*==== FISRT VALUE FOR ITEM TYPE i.e. BEFORE FIRST COMMA ======*/ SUBSTRING([item_type], 1, CHARINDEX(',', [item_type]) - 1) [item_type] /*==== FISRT VALUE FOR ITEM QTY i.e. BEFORE FIRST COMMA ======*/ , SUBSTRING([item_Qty], 1, CHARINDEX(',', [item_Qty]) - 1) [item_Qty] UNION ALL SELECT /*==== LAST VALUE FOR ITEM TYPE i.e. AFTER FIRST COMMA TO LENGTH OF THE VALUE ======*/ SUBSTRING([item_type], CHARINDEX(',', [item_type]) + 1, LEN([item_type])) [item_type] /*==== LAST VALUE FOR ITEM QTY i.e. AFTER FIRST COMMA TO LENGTH OF THE VALUE ======*/ , SUBSTRING([item_Qty], CHARINDEX(',', [item_Qty]) + 1, LEN([item_Qty])) [item_Qty] ) Splitter EDIT: Changed the code to be compatible with versions like SQL 2000 /*==== PIVOT THE ROWS INTO COLUMNS =================================*/ ;WITH PIVOTEDDATA AS ( SELECT [VUID] , MAX(CASE WHEN [FieldName] = 'item_Qty' THEN [FieldValue] ELSE '' END) [item_Qty] , MAX(CASE WHEN [FieldName] = 'item_type' THEN [FieldValue] ELSE '' END) [item_type] , MAX(CASE WHEN [FieldName] = 'CARBO' THEN [FieldValue] ELSE '' END) [CARBO] , MAX(CASE WHEN [FieldName] = 'CARBO_NEEDED' THEN [FieldValue] ELSE '' END) [CARBO_NEEDED] FROM @table AS T GROUP BY [VUID] ) /*======== CROSS APPLY TO GET THE SPLITTED DATA INTO COLUMNS =================================*/ SELECT [VUID] , Splitter.[item_type] , Splitter.[item_Qty] , [CARBO] , [CARBO_NEEDED] FROM PIVOTEDDATA CROSS APPLY ( SELECT /*==== FISRT VALUE FOR ITEM TYPE i.e. BEFORE FIRST COMMA ======*/ SUBSTRING([item_type], 1, CHARINDEX(',', [item_type]) - 1) [item_type] /*==== FISRT VALUE FOR ITEM QTY i.e. BEFORE FIRST COMMA ======*/ , SUBSTRING([item_Qty], 1, CHARINDEX(',', [item_Qty]) - 1) [item_Qty] UNION ALL SELECT /*==== LAST VALUE FOR ITEM TYPE i.e. AFTER FIRST COMMA TO LENGTH OF THE VALUE ======*/ SUBSTRING([item_type], CHARINDEX(',', [item_type]) + 1, LEN([item_type])) [item_type] /*==== LAST VALUE FOR ITEM QTY i.e. AFTER FIRST COMMA TO LENGTH OF THE VALUE ======*/ , SUBSTRING([item_Qty], CHARINDEX(',', [item_Qty]) + 1, LEN([item_Qty])) [item_Qty] ) Splitter
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Karthik Venkatraman avatar image
Karthik Venkatraman answered
Hi Usman, Thank you for the query.. Your query is working well in my local machine. but the problem is in my application server. since i have only select permission, i will not be able to execute queries having pivots due to the reason of db compatiblity. Is there any way to work around without using pivot tables?
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yes there is a workaround and I have edited my answer accordingly. But you did tag the question against SQL 2008 ;)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.