Selecting a Comma Delimited List with TSQL
Sometimes instead of bringing back a table set of information, a query needs to return a comma delimited list. I have seen some crazy stuff to do this like UDFs and Cursors. There is a really simple way to do this in a select statement though.
--declare a variable first
DECLARE @Orders varchar(MAX)
--varchar max is nice because there won't be overflow if the table is large
SET @Orders = '' --we can't concatenate NULLs so make empty string
SELECT @Orders = @Orders + Convert(varchar(10),Orders) + ','
FROM dbo.Orders
PRINT Left(@Orders,Len(@Orders)-1) --remove last comma
--declare a variable first
DECLARE @Orders varchar(MAX)
--varchar max is nice because there won't be overflow if the table is large
SET @Orders = '' --we can't concatenate NULLs so make empty string
SELECT @Orders = @Orders + Convert(varchar(10),Orders) + ','
FROM dbo.Orders
PRINT Left(@Orders,Len(@Orders)-1) --remove last comma
Hi,
Can we write the same query without using variable.
Reply to this
You could select the variable like SELECT @Orders as a second query in the proc.
You would have to do some kind of loop statement or a cursor.
Reply to this
Hi Adam Rink,
Need help on this.
Say,I have a table with two columns and with following data,
Col1 col2
-----------------
1 John
2 Jill
1 Smith
1 Jack
2 Smack
Now my query should return the values in the following way..
Col1 Col2
-------------------
1 John;Smith;Jack;
2 Jill;Smack;
I need help on this..its very urgent.. I tried using Userdefined functions, but its taking about 4-5 min
Reply to this
Good one, I'm racking my brain on it. I hate using cursors but this might be a case where need to. I'm not sure your record count or the performance of this, but it will work. I'll keep thinking on this one though.
DECLARE @temp table (col1 int, col2 varchar(MAX))
DECLARE @tempNames table (col1 int, col2 varchar(MAX))
INSERT INTO @tempNames (col1, col2) VALUES (1,'John')
INSERT INTO @tempNames (col1, col2) VALUES (2,'Jill')
INSERT INTO @tempNames (col1, col2) VALUES (1,'Smith')
INSERT INTO @tempNames (col1, col2) VALUES (1,'Jack')
INSERT INTO @tempNames (col1, col2) VALUES (2,'Smack')
INSERT INTO @temp (col1)
SELECT Distinct col1
FROM @tempNames
DECLARE @curCol int
DECLARE @commaList varchar(MAX)
DECLARE myCursor CURSOR
FOR SELECT DISTINCT col1
FROM @tempNames
OPEN myCursor
FETCH NEXT FROM myCursor INTO @curCol
WHILE @@FETCH_STATUS = 0
BEGIN
SET @commaList = ''
SELECT @commaList = @commaList + col2 + ','
FROM @tempNames
WHERE col1 = @curCol
UPDATE @temp
SET col2 = Left(@commaList,Len(@commaList)-1)
WHERE col1 = @curCol
FETCH NEXT FROM myCursor INTO @curCol
END
SELECT * FROM @temp
CLOSE myCursor
DEALLOCATE myCursor
Reply to this
CREATE TABLE #temp (SNo int identity ,empno varchar(20) not null,Domain_Name varchar(500))
CREATE TABLE #temp1 (Cnt int identity ,empno varchar(20) not null)
CREATE TABLE #GetEmpDomains (empno varchar(20) not null,Domain_Name varchar(2500))
insert into #temp(empno ,Domain_Name)
select distinct empno,Domain_Name from
Azviw_Sms
insert into #temp1(empno)
SELECT DISTINCT empno from emp_mstr
declare @vcnt int
declare @vloopcnt int
declare @vid int
declare @vtmpid int
declare @vdomains VARCHAr(50)
declare @vtmp1cnt INT
declare @vSecondLoopCnt int
declare @vSecondtblCnt int
declare @MainID varchar(20)
declare @vCurrentDomain varchar(50)
SET @vloopcnt = 1
SET @vSecondLoopCnt = 1
set @vcnt = (select count(*) from #temp)
SET @vtmp1cnt = (SELECT COUNT(*) FROM #temp1)
while(@vloopcnt <= @vtmp1cnt)
BEGIN
SELECT @MainID = empno FROM #temp1 WHERE Cnt = @vloopcnt
SELECT @vSecondtblCnt = COUNT(*) FROM #temp WHERE empno = @MainID
WHILE(@vSecondLoopCnt <= @vSecondtblCnt)
BEGIN
SET @vCurrentDomain = (SELECT TOP 1 Domain_Name FROM #temp WHERE empno = @MainID)
DELETE FROM #temp WHERE Domain_Name = @vCurrentDomain and empno = @MainID
SET @vdomains = ISNULL(@vdomains,'') + @vCurrentDomain + '; '
SET @vSecondLoopCnt = @vSecondLoopCnt + 1
END
INSERT INTO #GetEmpDomains (empno,Domain_Name)
SELECT @MainID,@vdomains
SET @vloopcnt = @vloopcnt + 1
SET @vSecondLoopCnt = 1
SET @vdomains = ''
END
select * from #GetEmpDomains
drop TABLE #temp
drop TABLE #temp1
drop TABLE #GetEmpDomains
Reply to this
As you can see in my previous query, my table contains about 5000 records.
And by using above query its taking about 10-13 seconds. for time being i can manage the things with the above query. but i came to know, without using temp tables and variables or cursors we can't acheive it.
Reply to this
You aren't going to get away from the variable or temp tables.
If you are worried about performance, I would suggest replacing the inner while loop with the variable select list in this case. It will cut down the scans. Other than that if you are using .Net or something to display the data, you might be able to use the SPLIT function.
SELECT @MainID = empno FROM #temp1 WHERE Cnt = @vloopcnt
SELECT @vSecondtblCnt = COUNT(*) FROM #temp WHERE empno = @MainID
WHILE(@vSecondLoopCnt <= @vSecondtblCnt)
BEGIN
SET @vCurrentDomain = (SELECT TOP 1 Domain_Name FROM #temp WHERE empno = @MainID)
DELETE FROM #temp WHERE Domain_Name = @vCurrentDomain and empno = @MainID
SET @vdomains = ISNULL(@vdomains,'') + @vCurrentDomain + '; '
SET @vSecondLoopCnt = @vSecondLoopCnt + 1
END
with
SELECT @MainID = empno FROM #temp1 WHERE Cnt = @vloopcnt
SELECT @vdomains= @vdomains + Domain_Name + ';'
FROM #temp
WHERE empno = @MainID
DELETE FROM #temp WHERE Domain_Name = @vCurrentDomain and empno = @MainID
Reply to this
Hi Adam Rink,
Thank you so much. It helped me.But there is some data mismatch. I am trying to find out that. Any ways thanks a lot for spending your valuable time on my query
Thanks,
Latha
Reply to this
It could be
DELETE FROM #temp WHERE Domain_Name = @vCurrentDomain and empno = @MainID
It should be
DELETE FROM #temp WHERE empno = @MainID
Because the select takes all the domain names with that MainID above and puts it into a list. Therefore we want to delete all the domains with that MainID.
Not sure if that is tripping you up.
Reply to this
Thanks Adam Rink. Its working now
Thanks for responding to my queries.
Regards,
Lathangi
Reply to this