Combining data in multiple rows into a single row

Question

The Table is in the following format:

ID     Category
1       a
1       b
2       a
2       b
2       c

I want the output in the following format

ID              Category
1                  a,b
2                 a,b,c

Please suggest a efficient way

possible duplicate of [Concatenate values based on ID](http://stackoverflow.com/questions/6603319/concatenate-values-based-on-id) — Mikael Eriksson, Mar 05 '12 at 10:46
Possible duplicate of [Concatenate many rows into a single text string?](https://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) — sandeep.gosavi, Sep 14 '17 at 13:31

score 0 · Answer 1 · answered Mar 05 '12 at 10:48

0

try the following query:

 SELECT ID,  
  REPLACE(
    (SELECT  Category AS [data()] 
     FROM TableA  
     WHERE b.ID=ID
     ORDER BY Category FOR XML PATH('')),
     ' ', ', ')
FROM TableB b 
GROUP BY ID

answered Mar 05 '12 at 10:48

Vikram

8,235
33
47

score 0 · Answer 2 · answered Mar 05 '12 at 10:50

0

I faced the similar thing last time and I solved it using Procedure. You need to do a loop in Category for particular ID.

answered Mar 05 '12 at 10:50

chinna_82

6,353
17
79
134

score 0 · Accepted Answer · edited Feb 20 '15 at 12:04

Maybe something like this:

Test data

DECLARE @tbl TABLE(ID INT,Category VARCHAR(100))
INSERT INTO @tbl
VALUES
    (1,'a'),
    (1,'b'),
    (2,'a'),
    (2,'b'),
    (2,'c')

Query

;WITH CTE     ----CTE Name
AS
(
    SELECT
        ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS RowNbr,
        ID,
        Category
    FROM
        @tbl AS tbl
)
SELECT
    CTE.ID,
    (
        STUFF
            ((
                SELECT
                    ','+Category
                FROM @tbl AS tbl
                WHERE tbl.ID=CTE.ID
                FOR XML PATH('')
                )
        ,1,1,'')
    ) AS Category
FROM
    CTE
WHERE
    CTE.RowNbr=1

Combining data in multiple rows into a single row

3 Answers3