SQL SELECT - group multiple values on a single row

sql, sql-server, sql-server-2005

Solution

You can use `FOR XML PATH`. While a bit hard to read, afaik it currently is the most concise way to concatenate strings.

Breakdown

- `FOR XML PATH` concatenates all values that match the subselect

- `STUFF` removes the leading comma

SQL Statement

SELECT  q.CAT
        , q.TYPE
        , SUBPRODUCT = STUFF((SELECT ', ' + qi.SUBPRODUCT FROM q qi WHERE qi.Cat = q.Cat AND qi.Type = q.Type FOR XML PATH('')), 1, 2, '')
FROM    q
GROUP BY
        q.CAT
        , q.TYPE

Test script

;WITH q AS (
  SELECT * FROM (VALUES 
    (1, 1, 1, 'AA')
    , (2, 1, 1, 'BB')
    , (3, 1, 2, 'CC')
  ) AS a (ID, CAT, TYPE, SUBPRODUCT)    
)
SELECT  q.CAT
        , q.TYPE
        , SUBPRODUCT = STUFF((SELECT ', ' + qi.SUBPRODUCT FROM q qi WHERE qi.Cat = q.Cat AND qi.Type = q.Type FOR XML PATH('')), 1, 2, '')
FROM    q
GROUP BY
        q.CAT
        , q.TYPE

Problem

I have a table with the following data: ``` ID CAT TYPE SUBPRODUCT --- ---- ------ ---------------- 1 1 1 AA 2 1 1 BB 3 1 2 CC ``` I'd like to get the data like this: ``` CAT TYPE SUBPRODUCT ----- ------ --------------- 1 1 AA,BB 1 2 CC ``` pls reply me what should i do. Thanks.

Original source