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
@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.
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
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?
Yes there is a workaround and I have edited my answer accordingly. But you did tag the question against SQL 2008 ;)
8 People are following this question.
