CODEWITHSUNDEEP
sqlsql-servert-sql
user3447031
user3447031

Reputation:

how to make comma separated values

This is my table structure

    create table #t(PK int,col1 varchar(10),col2 varchar(10))
  insert into #t values(1,'A','a'),(2,'B','b'),(3,'C','c'),(4,'A','d'),(5,'A','e'),(6,'B','f'),(88,'F','l'),(7,'C','g'),(8,'C','h'),(9,'D','k')

output column has to look like this 

col1    col2
A          a,d,e
B          b,f
C          c,g,h
D          k
F          l

How'd i get such an output ?

Upvotes: 1

Views: 82

Answers (2)

vhadalgi
vhadalgi

Reputation: 7189

drop table #t
create table #t(PK int,col1 varchar(10),col2 varchar(10))
insert into #t values(1,'A','a'),(2,'B','b'),(3,'C','c'),(4,'A','d'),(5,'A','e'),(6,'B','f'),(88,'F','l'),(7,'C','g'),(8,'C','h'),(9,'D','k')

select col1,SUBSTRING(d.col2,1,len(d.col2)-1)col2
from 
(
    select distinct col1 from #t
)a
cross apply(
select [col2]+',' from #t B where A.col1=B.col1
for xml path('')
)d (col2)

DEMO

Upvotes: 1

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Try this

select distinct t.[col1],
  STUFF((SELECT distinct ', ' + t1.col2
         from yourtable t1
         where t.[col1] = t1.[col1]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,2,'') department
from yourtable t;

Fiddle Demo

Upvotes: 0

Related Questions