Consolidate rows of data in SQL Server

Question

I have multiple rows of order data which i need to consolidate in one row per part.

An example is as follows:

OrderNum      PartNum      Qty
-------------------------------    
1             24            2
2             25           10
3             24            5
4             24           10

This then needs to be consolidated into:

OrderNum      PartNum      Qty
-------------------------------    
1, 3, 4       24           17
2             25           10

Does anybody have any ideas how I can do this?

I have had a look around online but cannot find a solution to this use case.

Many thanks in advance,

Answer 1

This can be done by grouping on PartNum, sum the quantities with SUM() and concatenating strings using FOR XML PATH('') in a correlated subquery. Using FOR XML PATH('') to concatenate string is explained in this answer on SO.

DECLARE @t TABLE(OrderNum INT, PartNum INT, Qty INT);
INSERT INTO @t(OrderNum,PartNum,Qty)
VALUES(1,24,2),(2,25,10),(3,24,5),(4,24,10);

SELECT
    OrderNum=STUFF((
        SELECT
            ','+CAST(i.OrderNum AS VARCHAR)
        FROM
            @t AS i
        WHERE
            i.PartNum=o.PartNum
        FOR XML
            PATH(''), TYPE
    ).value('.[1]','VARCHAR(MAX)'),1,1,''),
    o.PartNum,
    Qty=SUM(o.Qty)
FROM
    @t AS o
GROUP BY
    o.PartNum;

Result:

OrderNum | PartNum | Qty
------------------------
1,3,4    | 24      | 17
2        | 25      | 10