Bryan M. Bryan M. - 5 months ago 13
SQL Question

T-SQL How to GROUP BY two fields and concatenate another

I'm trying to group on 2 fields and return a third field that is a concatenation of the values of another field that the first 2 have in common, with a given ProductID. Here is my data:

ProductID Currency Price Territory
1 USD 6.99 US
1 EUR 4.99 GR
1 EUR 4.99 HU
1 EUR 4.99 LT
2 USD 7.99 US
2 EUR 5.99 GR
2 EUR 5.99 HU


I'd like results to come back like this:

ProductID Currency Price Territories
1 USD 6.99 US
1 EUR 6.99 GR, HU, LT


I can pull the Currenty and Price columns, but can't concatenate the Territories they have in common:

SELECT Currency, Price
FROM TerritoryPricing
WHERE ProductID = 1
GROUP BY Currency, Price


How can I concatenate the territories?

Answer

You can use SQL's XML processing to generate concatenated lists with SQL 2000 and up:

create table #TerritoryPricing ( ProductID int, Currency varchar(3), Price decimal(10,2), Territory varchar(2))
insert into #TerritoryPricing values (1,'USD',6.99,'US')
insert into #TerritoryPricing values (1,'EUR',4.99,'GR')
insert into #TerritoryPricing values (1,'EUR',4.99,'HU')
insert into #TerritoryPricing values (1,'EUR',4.99,'LT')
insert into #TerritoryPricing values (2,'USD',7.99,'US')
insert into #TerritoryPricing values (2,'EUR',5.99,'GR')
insert into #TerritoryPricing values (2,'EUR',5.99,'HU')

SELECT Currency, Price,
    SUBSTRING(
        (SELECT ( ',' + ltrim(rtrim(Territory)))  
        FROM #TerritoryPricing t2
        WHERE t1.Currency = t2.Currency  
            and t1.Price = t2.Price
            and ProductID = 1
        ORDER BY t2.Territory
        FOR XML PATH('')
        ), 2, 8000) Territories
FROM #TerritoryPricing t1
WHERE ProductID = 1
GROUP BY Currency, Price