format text

Stacy Bell 41 Reputation points
2022-09-24T20:30:29.667+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 102.2K Reputation points MVP
    2022-09-24T21:06:19.147+00:00

    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)  
    
    0 comments No comments

0 additional answers

Sort by: Most helpful