I have a question about SQL Server: how to convert rows to columns with comma-separated data using bulk data?
CREATE TABLE [dbo].[Emp]
(
[eid] [int] NULL,
[name] varchar NULL,
[sal] [money] NULL,
[doj] [date] NULL,
[deptno] [int] NULL
)
INSERT [dbo].[Emp] ([eid], [name], [sal], [doj], [deptno])
VALUES (1, N'a', 100.0000, CAST(N'2010-10-01' AS Date), 10)
INSERT [dbo].[Emp] ([eid], [name], [sal], [doj], [deptno])
VALUES (2, N'bb', 200.0000, CAST(N'2010-02-03' AS Date), 20)
INSERT [dbo].[Emp] ([eid], [name], [sal], [doj], [deptno])
VALUES (3, N'c', 300.0000, CAST(N'2017-02-03' AS Date), 30)
INSERT [dbo].[Emp] ([eid], [name], [sal], [doj], [deptno])
VALUES (4, N'd', 301.0000, CAST(N'2010-03-04' AS Date), 10)
INSERT [dbo].[Emp] ([eid], [name], [sal], [doj], [deptno])
VALUES (5, N'teee', 250.0000, CAST(N'2010-06-04' AS Date), 20)
INSERT [dbo].[Emp] ([eid], [name], [sal], [doj], [deptno])
VALUES (7, N'tte', 800.0000, CAST(N'2010-08-09' AS Date), 70)
I want output like below :
userlist
1,2,3,4,5,7
I tried this code:
select distinct
stuff((select ',' + cast(u.eid as varchar)
from emp u
where 1 = 1
for xml path('')), 1, 1, '') as userlist
from emp
Suppose if data has 100 thousand entries, then this query is not returning 100 thousand comma-separated ,its getting upto 8957 rows with comma separate,
Could you please tell me how to write query to achieve this task in SQL Server?