SQL Grouping, combining 2 rows to one cell

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 m开发者_如何学Ce having to write some sort of webpage to do it.

I hope this makes sense.

---

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.

---

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;

---

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