SQL Grouping, combining 2 rows to one cell

Question

I have some sql which is grouping data to summarize it.
Its basically a list of locations, items and quantities.

SELECT ItemCode, SUM(Qty) AS Qty
FROM Putaway 
GROUP BY ItemCode

Which is working as expected, giving a summary of the results.

ItemCode - Qty   
AAAA - 1
BBBB - 2
CCCC - 3

But I have been asked to give a list of locations for each of the items as well.

So basically I want to Generate the results to be like:

ItemCode - Qty - Locations
AAAA     -  1  - A1
BBBB     -  2  - B1, B2
CCCC     -  3  - B5, B6, B7

If I do the obvious - and just Add [Location] to the SELECT and Group By Then its giving me 2 rows for BBBB, 3 for CCCC etc. (Exactly as it should be, but not how I want it)

So, since there are multiple rows for each item in the table - how can I summarize it and combine the rows [Location]'s field into 1 field.

I am hoping for a simple SQL solution to avoid me having to write some sort of webpage to do it.

I hope this makes sense.

As @MatthewPK said, what is your DB? If you are on a recent instance of SQL Server, you could 'cheat' and use [PIVOT](http://msdn.microsoft.com/en-us/library/ms177410.aspx) — Widor, Sep 28 '11 at 16:33
@Widor: No. For aggregate concatenation can be used `SELECT ','+Location FROM ... FOR XML PATH('')`. — Bogdan Sahlean, Sep 28 '11 at 17:03

score 3 · Accepted Answer · answered Sep 28 '11 at 17:03

3

Provided that you're using SQL Server 2005 or newer, you can use FOR XML to do this bit for you:

create table Putaway (
    ItemCode    varchar(4),
    Qty         int,
    Location    varchar(3)
)

insert into Putaway
values ('AAAA', 1, 'A1'),
    ('BBBB', 1, 'B1'),
    ('BBBB', 1, 'B2'),
    ('CCCC', 1, 'B5'),
    ('CCCC', 1, 'B6'),
    ('CCCC', 1, 'B7')

SELECT ItemCode, SUM(Qty) AS Qty,
    (   SELECT x.Location + ', '
        FROM Putaway x
        WHERE p.ItemCode = x.ItemCode
        ORDER BY x.Location
        FOR XML PATH ('')
    ) as Locations
FROM Putaway p
GROUP BY ItemCode

Unfortunately, this results in an extra comma at the end, but you can easily trim that out in your GUI or by making it a sub-select.

answered Sep 28 '11 at 17:03

John N

1,755
17
21

1

Just for the record, there is a previos similar question (and answer) on SO: http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005 – bfavaretto Sep 28 '11 at 17:15
poo! got beaten to the answer :) hehe – Christian Sep 28 '11 at 17:21
I think there are probably a lot of similar answers to this on SO - it's one I see asked quite a lot. – John N Sep 28 '11 at 17:30
Thanks for the answer. I did search SO for a similar question, but I guess I used bad search terms! Legend tho! worked like a charm. – jb. Sep 29 '11 at 15:56

score 2 · Answer 2 · edited Jan 28 '16 at 05:48

2

This will not have extra comma at the end

SELECT ItemCode, SUM(Qty) AS Qty,
    Replace(  ( SELECT x.Location as [data()]
        FROM Putaway x
        WHERE p.ItemCode = x.ItemCode
        ORDER BY x.Location
        FOR XML PATH ('')) , ' ', ',') as Locations
FROM Putaway p
GROUP BY ItemCode

edited Jan 28 '16 at 05:48

Tharif

13,794
9
55
77

answered Aug 16 '13 at 18:01

Wilson

21
1

Christian · Answer 3 · 2011-09-28T17:22:43.397

2

SELECT p1.ItemCode, SUM(p1.Qty) AS Qty, (SELECT p2.location + ',' as 'data()' from putaway as p2 WHERE p1.itemcode LIKE p2.itemcode FOR xml path('')) AS Locations
FROM Putaway as p1
GROUP BY p1.itemcode;

edited Sep 28 '11 at 17:22

answered Sep 28 '11 at 17:17

Christian

3,708
3
39
60

SQL Grouping, combining 2 rows to one cell

3 Answers3