How to group concat in SQL Server

Question

I want a concatenate multiple priceband in the query (one [PoRequestLineKey] has multiple price bands)

SELECT DISTINCT
    [PoRequestLineKey],
    PriceBand
FROM  
    [mpp].[WindowSpecific]  WS
JOIN
    [nbi].[WebDomainPriceBand] WDP ON WS.PriceBandKey = WDP.ID 
GROUP BY 
    [PoRequestLineKey]

Does this answer your question? [How to concatenate text from multiple rows into a single text string in SQL server?](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) — Pred, May 27 '20 at 06:36

Karan · Answer 1 · 2020-05-27T06:06:56.420

If it is SQL Server 2017 you can use string_agg as below:

select [PoRequestLineKey], 
        string_agg([priceband], ', ') AS priceband
from [mpp].[WindowSpecific]  WS
JOIN [nbi].[WebDomainPriceBand] WDP ON
WS.PriceBandKey = WDP.ID 
Group by [PoRequestLineKey]

For older versions :

SELECT [PoRequestLineKey], 
    STUFF((
        SELECT ', ' + WDP2.[priceband]
        FROM [nbi].[WebDomainPriceBand] WDP2
        WHERE WS.PriceBandKey = WDP2.ID 
        FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,2,'') AS priceband
from [mpp].[WindowSpecific]  WS
JOIN [nbi].[WebDomainPriceBand] WDP ON
WS.PriceBandKey = WDP.ID 
Group by [PoRequestLineKey]

How to group concat in SQL Server

1 Answers1