Till today we are using scalar function to display Comma separated list with select statement. That scalar function use the COALESCE() to make comma separated list. Today I found wonderful solution to display comma separated list without scalar function. Let see that.
Scenario:
I have Table like:
CREATE TABLE #test(
field1 VARCHAR(5), field2 VARCHAR(5)
)
Lets insert some data in this table:
INSERT INTO #test
SELECT '001','AAA'
UNION ALL
SELECT '001','BBB'
UNION ALL
SELECT '002','CCC'
UNION ALL
SELECT '003','DDD'
UNION ALL
SELECT '004','EEE'
UNION ALL
SELECT '004','FFF'
UNION ALL
SELECT '004','GGG'
So now my table has Data like:
- Get Comma separated List
I want output like:
- Get Comma separated List
I come up with very good solution. Let me share with all of you:
SELECT field1,
SUBSTRING(
(
SELECT ( ', ' + field2)
FROM #test t2
WHERE t1.Field1 = t2.Field1
ORDER BY t1.Field1, t2.Field1
FOR XML PATH('')
), 3, 1000)
FROM #test t1
GROUP BY field1
- Get Comma separated List
My Output will be:
-
- Get Comma separated List
Please make comments, if this helps you in any way
Thanks Mr.Shah
ReplyDeleteThis is very helpful article for me. It has solved my problem very easily.
That was exactly what i wanted.
ReplyDeleteSimple and net.
Good work and i'm glad to have found this script.
Thanks
Super result Tejas. An amazingly simple solution.
ReplyDeleteMany thanks.
Can I suggest a small change to eliminate the character conversion that can occur, for example if we add this row:
ReplyDelete'004','&&&'
the output for 004 will be
004 "&&&,EEE,FFF"
To correct this, perhaps this way...
select tbl.field1,
substring(
(
SELECT ',' + field2 AS [text()]
FROM #test as t
WHERE t.field1 = tbl.field1
ORDER BY field1, field2
FOR XML PATH(''), type).value('(/text())[1]','varchar(max)'), 2, 1000)
from #test as tbl
group by tbl.field1
this will yield
004 "&&&,EEE,FFF"
Sorry the first output for the last comment should read...
ReplyDelete---
the output for 004 will be
004 "&&&,EEE,FFF"
Does Not Work!
ReplyDeleteIncorrect syntax near the keyword 'FOR'.
Hi,
ReplyDeletePlease let me know Which version of SQL you are using?
It should work with SQL 2005 and above
Tejas
Absolutely elegant! I have have been doing this the hard way for years.
ReplyDeleteI also get the error - Incorrect syntax near the keyword 'FOR'.
ReplyDeleteI'm using SQL 2000 which is probably the reason - darn as I really need this to work - any ideas?
Hi,
ReplyDeleteSQL SERVER 2000 is not supporting XML DATATYPE.
You can create one scalar function for the same, like:
CREATE FUNCTION [dbo].[GetCommaSeparatedList]
(
@ID AS BIGINT
)
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @result VARCHAR(MAX)
SELECT @result = COALESCE(@result + ', ', '') + ColumnName
FROM [TableName]
WHERE ID = @ID
RETURN @result
END
and use this function as:
SELECT [dbo].[GetCommaSeparatedList](1)
That is work around that people use for SQL SERVER 2000.
Thanks,
Tejas Shah
Thanks Tejas for your suggestion to use scalar function. I'll give it a try.
ReplyDeleteChristina
Hi Tejas,
ReplyDeleteThanks a lot for this post.Its very useful.
I have a similar situation where I need to get a comma separated list of a column values but only the unique values .
I have used your select query which is giving me below results : -
column1 column2 column3 column4 column5 column6
S09 E AO 21/10/2009 CRO CVT, DUB
S09 E AO 21/10/2009 ITA DES, DES, DES, DES, DES, DES, SII, SII
S09 E AO 23/10/2009 SWI INT, INT, INT, INT
S09 E AO 28/10/2009 ITA DES, DES, DES, DES, DES, PSC
I do get comma separated list for column6 but its repeating the duplicates .
Is there any way to get rid of duplicate values for the column with XML Path query ?
Please help me on this .
Kind Regards
Mandeep Bains
Hi Mandeep,
ReplyDeleteYou can do that with GROUP BY to identify UNIQUE names.
Write a query as:
SELECT field1,
SUBSTRING(
(
SELECT ( ',' + t2.field2)
FROM #test t2
WHERE t1.Field1 = t2.Field1
GROUP BY field2
FOR XML PATH('')
), 2, 1000)
FROM #test t1
GROUP BY field1
Thanks,
Tejas
This is working but performance is poor when result set grows. I Compared both methods and "COALESCE(@result + ', ', '') + ColumnName" method is fast.
ReplyDeleteThanks Tejas for the post.
ReplyDeleteThanks Tejas. This was extremely helpful for me.
ReplyDeleteKind Regards
Bernhard
(South Africa)
Hi Tejas This is Extremly Fine.
ReplyDeleteThis is exactly what i needed. Thank You.
ReplyDeletethat was amazing; i have invested 2 hours on a report and used colase in the function to achieve the same what you did in a single query .. simple brilleant
ReplyDeleteThanks.. This is what I searched for..
ReplyDeleteThanks man. This is what I was looking for and using variables wasn't an option for us.
ReplyDeleteThis is so cool...I struggled so much with coalese
ReplyDeleteAWESOME DUDE LOVE YOU....
ReplyDeleteNice Article ... Very helpful ... Brief & to the point ...
ReplyDeleteHow can this be done by using coalesce ?
ReplyDeleteHi there , I want the result as following :
ReplyDeleteCount
-------------------------------------------------
001 AA,BBB 2
002 CC 1
003 DD 1
004 EE,FFF,GGG 3
Any suggestions ???
I was getting the missing characters too. The problem is the code was missing a space after the comma, i.e. SELECT ( ',' + field2) should be SELECT ( ', ' + field2)
DeleteNote that there's no space in the text version (that you can copy and paste) but there is a space in the screenshot version just below it (at the time of this comment).
Hi Chirag,
ReplyDeleteCan you please provide your data to generate the expected output?
Tejas
SQLYoga
superb, very useful information . 10/10 :)
ReplyDeleteuseful solution..
ReplyDeleteThanks..
Useful solution..
ReplyDeleteThanks..
excellent!
ReplyDeleteLOVE YOU BOY :) ... It really worked for me. Dil chah raha ha pappi le looon aapki :)
ReplyDelete