0

I have a table like below ;


CREATE TABLE [dbo].[tempQeus](
    [EmpId] [bigint] NOT NULL,
    [PlanId] [bigint] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[tempQeus] ([EmpId], [PlanId]) VALUES (1872, 48)
GO
INSERT [dbo].[tempQeus] ([EmpId], [PlanId]) VALUES (1872, 60)
GO
INSERT [dbo].[tempQeus] ([EmpId], [PlanId]) VALUES (1872, 64)
GO
INSERT [dbo].[tempQeus] ([EmpId], [PlanId]) VALUES (1873, 49)
GO
INSERT [dbo].[tempQeus] ([EmpId], [PlanId]) VALUES (1873, 60)
GO
INSERT [dbo].[tempQeus] ([EmpId], [PlanId]) VALUES (1873, 64)
GO
INSERT [dbo].[tempQeus] ([EmpId], [PlanId]) VALUES (1874, 48)
GO
INSERT [dbo].[tempQeus] ([EmpId], [PlanId]) VALUES (1874, 60)
GO
INSERT [dbo].[tempQeus] ([EmpId], [PlanId]) VALUES (1874, 65)
GO

I need the output to be like below with comma separated;

for each EmpId the corresponding planId should be in one column with comma separated

Expected output;

EmpId      PlanId

1872       48,60,64
1873       49,60,64
1874       48,60,65

I have no clue where to start and i'm new to SQL and using SQL version 2016.

Mar1009
  • 721
  • 1
  • 11
  • 27
  • Upgrade to a supported version of SQL Server and use `string_agg()`. – Gordon Linoff May 13 '20 at 13:48
  • 1
    Why state *"using SQL version 2016"* and tag [tag:sql-server-2008]? Either way, the linked duplicate tells you everything you need. – Thom A May 13 '20 at 13:50
  • @GordonLinoff isn't `string_agg()` supported only on 2017 or higher? AFAIK, 2012 version is still supported... – Zohar Peled May 13 '20 at 13:53
  • @ZoharPeled . . . Yeah. But if you are going to go to a supported version, you might as well go to a recent one. – Gordon Linoff May 13 '20 at 14:00
  • SELECT EmpID,STUFF((SELECT ',' + PlanId FROM tempQeus EE WHERE EE.EmpID=E.EmpID FOR XML PATH('')), 1, 1, '') AS listStr FROM tempQeus E GROUP BY E.EmpID I tried this but receiving 'Error converting data type varchar to bigint.' – Mar1009 May 13 '20 at 14:17

0 Answers0