sql-server - 将分组的多个串联列返回到逗号分隔的字符串列

Question

我有下表

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

我想输出年份和类别的分组，并返回汽车名称和里程作为显示列

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

我正在尝试组合 a columns to comma delimited string有一个分组以及多个类型列连接，但我不知道如何继续。我使用的是 SQL Server 2012。

Answer 1

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

返回(感谢艾伦的表变量+1)

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