SQLTeam.com | Weblogs | Forums

How to convert rows to columns with commasepart using huge data in SQL Server

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?

I have to ask... why on this good Green Earth would you want 100,000 CSV elements in a single "cell" output? It can be done but it makes no sense. What is this going to be used for?

1 Like

delete purpose,we need to refer this table and delete tgt table data.
delete from emp where id in ( 1t0 10,0000).tgt db is not same its postgres so i need to put 1 lakh record in condition .

Instead of trying to build a list to be used here - I would recommend putting that data into a table and referencing the table.

DELETE
  FROM emp
 WHERE id IN (SELECT t.ID FROM deleteTable t)

The other option is to use the built in tools to split the CSV list into a table/array and use that in your query.

I'd use the method that the other "Jeff" posted just above this one. doing this with a 100,000 element CSV is just asking for major problems.