sql - Concatenate many rows into a single text string? -


consider database table holding names, 3 rows:

peter paul mary 

is there easy way turn single string of peter, paul, mary?

i had similar issue when trying join 2 tables one-to-many relationships. in sql 2005 found xml path method can handle concatenation of rows easily.

if there table called students

subjectid       studentname ----------      ------------- 1               mary 1               john 1               sam 2               alaina 2               edward 

result expected was:

subjectid       studentname ----------      ------------- 1               mary, john, sam 2               alaina, edward 

i used following t-sql:

select main.subjectid,        left(main.students,len(main.students)-1) "students"     (         select distinct st2.subjectid,              (                 select st1.studentname + ',' [text()]                 dbo.students st1                 st1.subjectid = st2.subjectid                 order st1.subjectid                 xml path ('')             ) [students]         dbo.students st2     ) [main] 

you can same thing in more compact way if can concat commas @ beginning , use substring skip first 1 don't need subquery:

select distinct st2.subjectid,      substring(         (             select ','+st1.studentname  [text()]             dbo.students st1             st1.subjectid = st2.subjectid             order st1.subjectid             xml path ('')         ), 2, 1000) [students] dbo.students st2 

Comments

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

python Tkinter Capturing keyboard events save as one single string -

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -