35

My T-SQL query generates following result set

ID        Date
756 2011-08-29
756 2011-08-31
756 2011-09-01
756 2011-09-02

How can I convert like this

ID                Date
756 2011-08-29, 2011-08-31, 2011-09-01, 2011-09-02

Any suggestion would be appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
poshan
  • 3,069
  • 5
  • 20
  • 30

1 Answers1

85

You could use FOR XML PATH and STUFF to concatenate the multiple rows into a single row:

select distinct t1.id,
  STUFF(
         (SELECT ', ' + convert(varchar(10), t2.date, 120)
          FROM yourtable t2
          where t1.id = t2.id
          FOR XML PATH (''))
          , 1, 1, '')  AS date
from yourtable t1;

See SQL Fiddle with Demo

Taryn
  • 242,637
  • 56
  • 362
  • 405
  • 5
    This gave me a starting space character. I fixed it by changing `, 1, 1, '') AS date` to `, 1, 2, '') AS date`. – voidstate Aug 01 '17 at 14:57
  • This is neat, but it's tripping me up that the inner select somehow is referencing a variable (t1) from the outer select. What is going on here? – Michael Aug 01 '22 at 18:07
  • @Michael - the column from the outer select is referenced in the inner basically as a cheap join. It's sort of like an EXISTS with a sub query. – PKD Nov 10 '22 at 20:41
  • @Taryn - I use these all the time, but the problem with them is that they aren't cheap. The output time of the overall query increases greatly depending on the volume of data in the stuffed sub query. Honestly hoping for a better way that isn't as costly. – PKD Nov 10 '22 at 20:42