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,

By "consolidation" you mean concatenate values? You won't find anything if you use the wrong keywords. In this case you seem to be grouping by ParNum, summing quantities and concatenating order numbers — Panagiotis Kanavos, Nov 24 '16 at 12:13

score 4 · Answer 1 · answered Nov 24 '16 at 12:15

Try this

SELECT STUFF((SELECT ',' + CAST(OrderNum AS VARCHAR(4)) 
              FROM mytable AS s
              WHERE s.PartNum = t.PartNum
              FOR XML PATH('')), 1, 1, '') AS OrderNum
       PartNum, SUM(Qty)
FROM mytable AS t
GROUP BY PartNum

score 2 · Accepted Answer · edited May 23 '17 at 12:09

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

captainsac · Answer 3 · 2016-11-25T04:57:06.890

DECLARE @t TABLE(OrderNum INT, PartNum INT, Qty INT)

INSERT INTO @t VALUES(1    ,         24   ,        2)
INSERT INTO @t VALUES(2    ,         25   ,        10)
INSERT INTO @t VALUES(3    ,         24   ,        5)
INSERT INTO @t VALUES(4    ,         24   ,        10)

SELECT OrderNum = 
    STUFF((SELECT ', ' + CONVERT(VARCHAR(50),OrderNum)
           FROM @t b 
           WHERE b.PartNum = a.PartNum 
          FOR XML PATH('')), 1, 2, ''),
          PartNum,
          SUM(Qty) as Qty
FROM @t a
GROUP BY PartNum

Result

score 2 · Answer 4 · answered Nov 24 '16 at 12:21

2

SQL Server 2016 added the STRING_AGG function.

In your case, you could write

select STRING_ACC(OrderId,','),PartNum, Sum(Qty)
from MyTable
Group by PartNum

For earlier versions you'd have to use one of the techniques described by Aaron Bertrand in Grouped Concatenation in SQL Server. The fastest is to use a SQLCLR method. Next comes the FOR XML method posted by @GiorgosBetsos

answered Nov 24 '16 at 12:21

Panagiotis Kanavos

120,703
13
188
236

1

STRING_AGG is vNext not 2016 isn't it? – Rich Benner Nov 24 '16 at 12:27
@RichBenner The [documentation](https://msdn.microsoft.com/en-us/library/mt790580.aspx) states vNext, indeed. – TT. Nov 24 '16 at 12:30
I have upvoted this as it could be helpful to someone else but unfortunately i am not on 2016 :( – DaRoGa Nov 24 '16 at 13:21

score 1 · Answer 5 · answered Dec 31 '16 at 13:07

There are many ways to do this.

create table tablename (Name varchar(100), Rnk int)
Insert into tablename values
('Northshore', 1),
('F3', 2),
('Borderline', 3),
('Mattoon',3),
('Vinemane',5),
('Arizona',5),
('WestShore', 5),
('Schumburg', 5),
('Wilson',5)

--Method2
Select distinct 
   names=  REPLACE( 
                ( 
          Select a.Name as [data()]
       From tablename A
    Where A.Rnk = b.Rnk
    Order by a.Name
    FOR XML PATH ('') ), ' ', ',') ,Rnk

    From tablename B Order by Rnk

OR

CREATE TABLE TestTable (ID INT, Col VARCHAR(4))
GO
INSERT INTO TestTable (ID, Col)
SELECT 1, 'A'
UNION ALL
SELECT 1, 'B'
UNION ALL
SELECT 1, 'C'
UNION ALL
SELECT 2, 'A'
UNION ALL
SELECT 2, 'B'
UNION ALL
SELECT 2, 'C'
UNION ALL
SELECT 2, 'D'
UNION ALL
SELECT 2, 'E'
GO
SELECT *
FROM TestTable
GO


-- Get CSV values
SELECT t.ID, STUFF(
(SELECT ',' + s.Col
FROM TestTable s
WHERE s.ID = t.ID
FOR XML PATH('')),1,1,'') AS CSV
FROM TestTable AS t
GROUP BY t.ID
GO

OR

CREATE  TABLE  #mable(mid INT, token nvarchar(16))

INSERT INTO #mable VALUES (0, 'foo')
INSERT INTO #mable VALUES(0, 'goo')
INSERT INTO #mable VALUES(1, 'hoo')
INSERT INTO #mable VALUES(1, 'moo')


SELECT m1.mid,
       ( SELECT m2.token + ','
           FROM #mable m2
          WHERE m2.mid = m1.mid
          ORDER BY token
            FOR XML PATH('') ) AS token
  FROM #mable m1
 GROUP BY m1.mid ;

Also, see this.

http://blog.sqlauthority.com/2009/11/25/sql-server-comma-separated-values-csv-from-table-column/

In your second example, consider the values e.g. to be `'', '', ''` etc. You'll see that concatenating the way you are doing, leads to output with `<` substituted by `<` and `>` by `>`. To avoid this, select with `FOR XML PATH(''), TYPE` (i.e. result as XML type) and apply the `value('.[1]','VARCHAR(MAX)')` function to it to get proper results. — TT., Dec 31 '16 at 14:37

Consolidate rows of data in SQL Server

5 Answers5