Return grouped multiple concatenated columns to a comma delimited string column

Question

I have the following table

Year, Category,   CarID,  CarName,   Milage
--------------------------------------------
2012  GroupA       1     Porsche      100
2012  GroupA       2     Mercedes     200
2013  GroupA       3     Ferrari      300
2013  GroupB       4     Uno          200
2013  GroupB       5     Beetle       200

I want to output grouping on Year and Category and return the Car Names and milage as a display column

Year, Category, DisplayString
--------------------------------------------
2012  GroupA    Mercedes (200km), Porsche (100km)  
2013  GroupA    Ferrari (300km)
2013  GroupB    Beetle (200km), Uno (200km)

I am trying to combine a columns to comma delimited string with a group by as well as more than one type column concatenated but I'm not sure how to proceed. I'm using SQL Server 2012.

John Cappelletti · Accepted Answer · 2016-12-14T19:15:58.080

Select A.*
      ,DistplayString = (Select Stuff((Select Distinct concat(', ',CarName,' (',Milage,'km)') 
                                        From  YourTable 
                                        Where Year=A.Year and Category=A.Category 
                                        For XML Path ('')),1,2,'') )
 From (Select Distinct Year, Category From YourTable) A

Returns (Thanks to Alan's Table Variable +1)

Year    Category    DistplayString
2012    GroupA      Mercedes (200km), Porsche (100km)
2013    GroupA      Ferrari (300km)
2013    GroupB      Beetle (200km), Uno (200km)

score 2 · Answer 2 · answered Dec 14 '16 at 19:05

John beat me to it. This is pretty much the same solution under the hood.

-- Your Sample Data
DECLARE @yourtable TABLE 
(
  [year]   smallint, 
  category varchar(10), 
  CarID    int, 
  CarName  varchar(20), 
  Milage   int
);

INSERT @yourtable
VALUES 
(2012,'GroupA',1,'Porsche',  100),
(2012,'GroupA',2,'Mercedes', 200),
(2013,'GroupA',3,'Ferrari',  300),
(2013,'GroupB',4,'Uno',      200),
(2013,'GroupB',5,'Beetle',   200);

-- Solution
SELECT 
  [Year], 
  Category, 
  DisplayString = 
  STUFF
  ((
    SELECT CONCAT(', ', Carname, ' (', milage, 'km)')
    FROM @yourtable i WHERE o.[year] = i.[year] AND o.category = i.category
    FOR XML PATH('')
  ),1,2,'')
FROM @yourtable o
GROUP BY [year], category;

Returns:

Year   Category   DisplayString
------ ---------- ---------------------------------
2012   GroupA     Porsche (100km), Mercedes (200km)
2013   GroupA     Ferrari (300km)
2013   GroupB     Uno (200km), Beetle (200km)

Return grouped multiple concatenated columns to a comma delimited string column

2 Answers2