SQL - group by - merge a column's values into one row?

Question

(Working in SSMS2016) I have a table as below:

| ORDER_NUMBER | ITEM_CAT| ORDER_QTY | UNALLOCATED|
|--------------|---------|-----------|------------|
|  1           | FLAT    | 2         | 1          |
|  1           | HANG    | 1         | 1          |
|  1           | SHOE    | 2         | 1          |
|  2           | FLAT    | 1         | 1          |
|  2           | FLAT    | 1         | 1          |
|  3           | SHOE    | 1         | 1          |
|  3           | SHOE    | 1         | 1          |
|  3           | SHOE    | 1         | 1          |
|  4           | FLAT    | 1         | 1          |
|  4           | SHOE    | 1         | 1          |
|  4           | FLAT    | 1         | 1          |
|  4           | SHOE    | 1         | 1          |

If I do

SELECT ORDER_NUMBER, ITEM_CAT, SUM(ORDER_QTY) AS 'ORDER_QTY', SUM(UNALLOCATED) AS 'UNALLOCATED' 
FROM TABLEA 
GROUP BY ORDER_NUMBER, ITEM_CAT

I get the below:

| ORDER_NUMBER | ITEM_CAT| ORDER_QTY | UNALLOCATED|
|--------------|---------|-----------|------------|
|  1           | FLAT    | 2         | 1          |
|  1           | HANG    | 1         | 1          |
|  1           | SHOE    | 2         | 1          |
|  2           | FLAT    | 2         | 2          |
|  3           | SHOE    | 3         | 3          |
|  4           | FLAT    | 2         | 2          |
|  4           | SHOE    | 2         | 2          |

Ideally, I'm looking to get just a single row for each ORDER_NUMBER, and list the unique ITEM_CAT values in the same row, so something like:

| ORDER_NUMBER | ITEM_CAT             | ORDER_QTY | UNALLOCATED|
|--------------|----------------------|-----------|------------|
|  1           | FLAT / HANG / SHOE   | 5         | 3          |
|  2           | FLAT                 | 2         | 2          |
|  3           | SHOE                 | 3         | 3          |
|  4           | FLAT / SHOE          | 4         | 4          |

Can this be easily achieved?

Thanks.

Although this may be a duplicate of some question, it is not a duplicate of the suggested one. The functionality of SQL Server has changed since SQL Server 2005, rendering that solution obsolete. — Gordon Linoff, Jan 20 '18 at 15:15

score 3 · Accepted Answer · answered Jan 20 '18 at 15:13

3

If you are using SQL Server 2016, you can use string_agg():

SELECT ORDER_NUMBER,
       STRING_AGG(ITEM_CAT, ' / ') as ITEM_CATS,
       SUM(ORDER_QTY) AS ORDER_QTY, 
       SUM(UNALLOCATED) AS UNALLOCATED
FROM (SELECT ORDER_NUMBER, ITEM_CAT, SUM(ORDER_QTY) AS ORDER_QTY,
             SUM(UNALLOCATED) AS UNALLOCATED
      FROM TABLEA
      GROUP BY ORDER_NUMBER, ITEM_CAT
     ) a
GROUP BY ORDER_NUMBER;

answered Jan 20 '18 at 15:13

Gordon Linoff

1,242,037
58
646
786

Thanks very much. I think string_agg() was added in 2017 (or possibly a later version of 2016 than I have), so unfortunately I couldn't use that. It did however lead me to find "STUFF...FOR XML PATH", which I managed to get exactly what I needed after a bit of trial and error. – Smeghead Jan 21 '18 at 02:12

SQL - group by - merge a column's values into one row?

1 Answers1