Return table values as a comma separated list within a GROUP BY

Question

I want select data from a table (SQL Server 2012) -- grouping one field and returning all the duplicates within that group as a comma separated list.

All the solutions I have seen so far do not allow for a group and return a single comma separated list for the whole table as a variable, which doesn't work for me.

What SQL do I need?

This is the data I have:

BankingSlipID   CashSheetSessionID  BankingSlipNumber
1               10                  abcabc
2               10                  xyzxyz
3               10                  123465789
4               11                  9999999
5               11                  55555
6               12                  88888

This is what I want to get:

CashSheetSessionID  BankingSlipNumber
10                  abcabc, xyzxyz, 123465789
11                  9999999, 55555
12                  88888

SQL to create the source table (with data):

CREATE TABLE [dbo].[BankingSlips](
[BankingSlipID] [int] IDENTITY(1,1) NOT NULL,
[CashSheetSessionID] [int] NOT NULL,
[BankingSlipNumber] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[BankingSlips] ON
GO
INSERT [dbo].[BankingSlips] ([BankingSlipID], [CashSheetSessionID], [BankingSlipNumber]) VALUES (1, 10, N'abcabc')
INSERT [dbo].[BankingSlips] ([BankingSlipID], [CashSheetSessionID], [BankingSlipNumber]) VALUES (2, 10, N'xyzxyz')
INSERT [dbo].[BankingSlips] ([BankingSlipID], [CashSheetSessionID], [BankingSlipNumber]) VALUES (3, 10, N'123465789')
INSERT [dbo].[BankingSlips] ([BankingSlipID], [CashSheetSessionID], [BankingSlipNumber]) VALUES (4, 11, N'9999999')
INSERT [dbo].[BankingSlips] ([BankingSlipID], [CashSheetSessionID], [BankingSlipNumber]) VALUES (5, 11, N'55555')
INSERT [dbo].[BankingSlips] ([BankingSlipID], [CashSheetSessionID], [BankingSlipNumber]) VALUES (6, 12, N'88888')
SET IDENTITY_INSERT [dbo].[BankingSlips] OFF
GO

What SQL do I need to write to get that?

score 1 · Accepted Answer · answered Jun 30 '15 at 14:13

Use STUFF

SELECT CashSheetSessionID, BankingSlipNumber = 
    STUFF((SELECT ', ' + BankingSlipNumber
           FROM BankingSlips b 
           WHERE b.CashSheetSessionID = a.CashSheetSessionID 
          FOR XML PATH('')), 1, 2, '')
FROM BankingSlips a
GROUP BY CashSheetSessionID

Output:

CashSheetSessionID  BankingSlipNumber
10                  abcabc, xyzxyz, 123465789
11                  9999999, 55555
12                  88888

SQL Fiddle: http://sqlfiddle.com/#!3/1d728b/4/0

Return table values as a comma separated list within a GROUP BY

1 Answers1