Geek asks and answers

SQL SELECT - groups several values ​​in one row

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 would like to receive the following data:

CAT   TYPE   SUBPRODUCT
----- ------ ---------------
1     1      AA,BB
1     2      CC

pls tell me what should i do.

Thank.

+3
source share
1 answer

You can use FOR XML PATH. Although a little hard to read, afaik is currently the shortest way to concatenate strings.

Structure

  • FOR XML PATH combines all values ​​matching a subquery
  • STUFF removes 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
+7
source

More articles:


All Articles