Get the records in comma separator

Question

Post reply

Get the records in comma separator

sqlusers

SSCrazy

Points: 2463
More actions
July 8, 2011 at 4:41 am

#241141

Hi,
DECLARE @TABLE TABLE (fldName1 VARCHAR(8), fldName2 VARCHAR(8))
INSERT INTO @TABLE
SELECT 'Test1','Name1' UNION ALL
SELECT 'Test1','Name2' UNION ALL
SELECT 'Test2','Name1' UNION ALL
SELECT 'Test3','Name1' UNION ALL
SELECT 'Test3','Name2'
SELECT * FROM @TABLE
My End result should be as:
>>> Test1,Name1,Name2,Test2,Name1,Test3,Name1,Name2

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

winash SSChampion Points: 11446 More actions · Answer 1

Adapted from one of the methods explained in this[/url] article:

DECLARE @TABLE TABLE (fldName1 VARCHAR(8), fldName2 VARCHAR(8))

INSERT INTO @TABLE(fldName1,fldName2)

SELECT 'Test1','Name1' UNION ALL

SELECT 'Test1','Name2' UNION ALL

SELECT 'Test2','Name1' UNION ALL

SELECT 'Test3','Name1' UNION ALL

SELECT 'Test3','Name2'

SELECT * FROM @TABLE;

-- the concatentation select

SELECT t1.fldName1,STUFF((SELECT ','+t2.fldName2

FROM @TABLE t2 WHERE t1.fldName1= t2.fldName1 FOR XML PATH('')),1,1,'')

FROM @TABLE t1 GROUP BY t1.fldName1;

DECLARE @sql nvarchar(max) = '';

SELECT @sql = @sql+CASE WHEN @sql= '' THEN '' ELSE ',' END +t1.fldName1+','+

STUFF((SELECT ','+t2.fldName2

FROM @TABLE t2 WHERE t1.fldName1= t2.fldName1

FOR XML PATH('')),1,1,'')

FROM @TABLE t1 GROUP BY t1.fldName1;

-- the concatenated string

SELECT @sql;

tommyh SSCertifiable Points: 6302 More actions · Answer 2

Or another option

select

Stuff( (select distinct

',' + t.fldName1 + ',' +

Stuff((SELECT ',' + fldName2

FROM @TABLE t2

where t2.fldName1 = t.FldName1

for XML path('')), 1, 1, '')

from @table t

for xml path(''))

, 1, 1, '')

/T

sqlusers SSCrazy Points: 2463 More actions · Answer 3

Hi,

Thanks winash, tommyh for your quick solution. I have one more with me...

DECLARE @T VARCHAR(888)

SET @T = ''

DECLARE @TABLE TABLE (fldName1 VARCHAR(8), fldName2 VARCHAR(8))

INSERT INTO @TABLE

SELECT 'Test1','Name1' UNION ALL

SELECT 'Test1','Name2' UNION ALL

SELECT 'Test2','Name1' UNION ALL

SELECT 'Test3','Name1' UNION ALL

SELECT 'Test3','Name2'

SELECT * FROM @TABLE

SELECT @T = REPLACE(@T,',,',',') + CASE WHEN (ROW_NUMBER() OVER (PARTITION BY fldName1

ORDER BY fldName1)) > 1 THEN '' ELSE fldName1 END + ',' + fldName2 + ','

FROM @TABLE

SELECT @T = REPLACE(SUBSTRING(@T,1,LEN(@T) - 1),',,',',')

SELECT @T

Thanks Guys for your timely help!!!

kramaswamy SSCoach Points: 18181 More actions · Answer 4

The XML solution is the better of the choices, if you're going to be dealing with a large string. I remember one application I was developing was to build sitemaps for a website, through a fairly large list of URLs. By doing the "concatenation select" version, the query ended up taking a *very* long time once the size of the string grew past a certain threshold.