SQL Server Concatenate To New Column With SUM

Question

Please consider the following sample data:

Part Loc Qty
A A-1 10
A A-5 15
A B-4 7
B B-5 10
B B-6 15

I can query easily enough to get the location qty for each part:

select
    pt.Part
    ,lc.Loc
    ,lc.LocQty Qty
from
    Part pt
left join
    Loc lc
on
    pt.Part = lc.Part

I can also sum the locations like so:

select
    pt.Part
    ,sum(lc.LocQty) TotalQty
from
    Part pt
left join
    Loc lc
on
    pt.Part = lc.Part
group by
    pt.Part

What I want to do now is instead concatenate into a new column, each location that the part appears, like so:

Part TotalQty InLocations
A 32 A-1, A-5, B-4
B 25 B-5, B-6

What would be the best approach to this?

Many thanks

@HoneyBadger The tag specifically says SQL Server 2008. `STRING_AGG` didn't exist in the old days before SQL Server 2017 — Martin, Nov 14 '19 at 08:09
@Martin, yes, that's why OP should put that in to a search engine, it'll spit out alternatives — HoneyBadger, Nov 14 '19 at 08:10
Possible duplicate of [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — Martin, Nov 14 '19 at 08:11
@HoneyBadger Fair enough, but the top results reference `STRING_AGG`, which I think is confusing if you aren't aware that this is not available in SQL Server 2008. — Martin, Nov 14 '19 at 08:12
The GROUP-CONCAT available in MySQL is exactly what I am trying to do. — A Kay, Nov 14 '19 at 08:19
@AKay Did you take a look at the duplicate I linked? The accepted answer has the exact method for achieving this pre-SQL Server 2017. — Martin, Nov 14 '19 at 08:24

score 1 · Answer 1 · answered Nov 14 '19 at 08:32

SQL SERVER replacement of GROUP_CONCAT() is STRING_AGG().But STRING_AGG() was introduced in SQL Server 2017 version, you have to use combination of STUFF and For XML PATH to get your result.

SELECT P.PART,SUM(QTY) AS TOTALQTY,
       INLOCATIONS = STUFF(
                     (
                      SELECT ', ' + PA.LOC 
                      FROM PART AS PA 
                      WHERE PA.PART = P.PART
                      FOR XML PATH('')
                     ), 1, 2, N''
                     ) 
 FROM PART P
 GROUP BY P.PART

Check Demo here

SQL Server Concatenate To New Column With SUM

1 Answers1