How to get column values comma seperatedly

Question

How to get column values comma seperatedly in SQL Server. Below shown is my sql query#

 select BandName from BandMaster where BandId<100

I need to get the BandName values comma seperatedly in a single query.

possible duplicate of [How to make a list of T-SQL results with comma's between them?](http://stackoverflow.com/questions/9726660/how-to-make-a-list-of-t-sql-results-with-commas-between-them) — Karl Kieninger, Mar 19 '14 at 04:52
Also a duplicate of: http://stackoverflow.com/questions/12231439/need-faster-concat-of-columns-in-sql-server-table — Solomon Rutzky, Mar 19 '14 at 04:55

Kailas · Accepted Answer · 2014-03-19T05:46:16.323

1

Use XML path for this.

select (select BandName+',' from BandMaster where BandId<100 for xml path('')) as NewColumnName

Try this

edited Mar 19 '14 at 05:46

answered Mar 19 '14 at 05:01

Kailas

439
1
5
14

It will return in XML format not in comma seprated. – AK47 Mar 19 '14 at 05:09
It will return comma seperated itself. Pls try out, and then comment – Kailas Mar 19 '14 at 05:11
Yes friend, I have tried out & then commented.It is comminig like this AB. Am i missing on something? – AK47 Mar 19 '14 at 05:19
No my frined, I know you haven't tried :).If you gave like this '**select BandName from BandMaster where BandId<100 for xml path('')'**, then it would be coming in the way you specified, I have specified a **select** in the outer query , to by pass the xml tags.Please try out – Kailas Mar 19 '14 at 05:33
Is this correct? select (select status_name from status_master where isfollowup=4 for xml path('')) as NewColumnName – AK47 Mar 19 '14 at 05:38
1

**select (select status_name+',' from status_master where isfollowup=4 for xml path('')) as NewColumnName**..Please try out – Kailas Mar 19 '14 at 05:47
Ya, now it working! So you that means you provided solution without Testing. Isn't it?Still Good solution! – AK47 Mar 19 '14 at 05:50
T just wrote the query and checked whether result is coming.Didnt cross check the resut.My mistake – Kailas Mar 19 '14 at 05:53

score 0 · Answer 2 · answered Mar 19 '14 at 04:54

Try this

SELECT BandName  = 
              STUFF ( ( SELECT ','+InrTab.BandName 
                        FROM [Table1] InrTab
                        WHERE InrTab.BandId= OutTab.BandId
                                            AND InrTab.BandId < 100                     
                        ORDER BY InrTab.BandId
                        FOR XML PATH(''),TYPE 
                       ).value('.','VARCHAR(MAX)') 
                      , 1,1,SPACE(0))
FROM [Table1] OutTab
GROUP BY OutTab.BandId;

score 0 · Answer 3 · answered Mar 19 '14 at 04:55

0

Please Try this

SELECT GROUP_CONCAT(BandName) from BandMaster where BandId<100.

answered Mar 19 '14 at 04:55

Mad Angle

2,347
1
15
33

No GROUP_CONCAT in SQL SEREVR – Vignesh Kumar A Mar 19 '14 at 04:56

score 0 · Answer 4 · answered Mar 19 '14 at 05:10

0

Declare @retStr varchar(max) = ''
select @retStr = @retStr + BandName + ',' from BandMaster
where BandId<100 

Select @retStr

answered Mar 19 '14 at 05:10

AK47

3,707
3
17
36

How to get column values comma seperatedly

4 Answers4