(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.
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;