comma delimited list as a single string, T-SQL [duplicate]

Question

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.

Taryn · Accepted Answer · 2014-10-20 09:52:39Z

91

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

edited Oct 20, 2014 at 9:52

answered Aug 14, 2013 at 15:24

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

voidstate Over a year ago

This gave me a starting space character. I fixed it by changing , 1, 1, '') AS date to , 1, 2, '') AS date.

Michael Over a year ago

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?

PKD Over a year ago

@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 Over a year ago

@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.

Collectives™ on Stack Overflow

comma delimited list as a single string, T-SQL [duplicate]

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related