format text

Question

CREATE TABLE [dbo].Order ON [PRIMARY]
GO
INSERT [dbo].[TestOrder] ([category], [name], [order]) VALUES (N'Outdoor', N'chair', 5)
INSERT [dbo].[TestOrder] ([category], [name], [order]) VALUES (N'Outdoor', N'table', 2)
INSERT [dbo].[TestOrder] ([category], [name], [order]) VALUES (N'Outdoor', N'rug', 1)
INSERT [dbo].[TestOrder] ([category], [name], [order]) VALUES (N'Outdoor', N'lamp', 4)
INSERT [dbo].[TestOrder] ([category], [name], [order]) VALUES (N'Outdoor', N'pot', 3)
INSERT [dbo].[TestOrder] ([category], [name], [order]) VALUES (N'Indoor', N'plant', 1)
INSERT [dbo].[TestOrder] ([category], [name], [order]) VALUES (N'Indoor', N'bed', 4)
INSERT [dbo].[TestOrder] ([category], [name], [order]) VALUES (N'Indoor', N'dresser', 2)
INSERT [dbo].[TestOrder] ([category], [name], [order]) VALUES (N'Indoor', N'fan', 3)
INSERT [dbo].[TestOrder] ([category], [name], [order]) VALUES (N'Indoor', N'blender', 5)
GO

How do return the results in 2 rows like this?
Indoor: plant,dresser,fan,bed,blender
Outdoor: rug,table,pot,lamp,chair

Answer 1

On SQL 2017 and later, this is straightforward with the string_agg aggregate:

   SELECT category, string_agg(name, ',') WITHIN GROUP (ORDER BY "order")  
   FROM   TestOrder  
   GROUP  BY category  

On earlier versions you can use a kludge with XML:

   SELECT cat.category, substring(t.list, 1, len(t.list) - 1)  
   FROM   (SELECT DISTINCT category FROM TestOrder) AS cat  
   CROSS  APPLY (SELECT t.name + ','  
                 FROM   TestOrder t  
                 WHERE  cat.category = t.category  
                 ORDER  BY t."order"  
                 FOR XML PATH('')) AS t(list)