x

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

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

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

raw.jpg (46.9 kB)
outout.jpg (25.1 kB)
more ▼

asked Jul 15, 2012 at 12:16 PM in Default

Karthik Venkatraman gravatar image

Karthik Venkatraman
72 7 9 10

@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.
Jul 16, 2012 at 04:52 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left

2 answers: sort oldest

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
more ▼

answered Jul 16, 2012 at 07:03 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(comments are locked)
10|1200 characters needed characters left

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?
more ▼

answered Jul 16, 2012 at 08:20 AM

Karthik Venkatraman gravatar image

Karthik Venkatraman
72 7 9 10

Yes there is a workaround and I have edited my answer accordingly. But you did tag the question against SQL 2008 ;)
Jul 16, 2012 at 09:11 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1832
x369
x77
x45
x35

asked: Jul 15, 2012 at 12:16 PM

Seen: 1926 times

Last Updated: Jul 16, 2012 at 09:11 AM