Display comma seperated values in a single column as individual rows

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?