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.
Add a comment |
1 Answer
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; 
4 Comments
voidstate
This gave me a starting space character. I fixed it by changing
, 1, 1, '') AS date to , 1, 2, '') AS date.
Michael
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
@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
@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.