2

I have a values in a table as shown below. I need to get the generation times in comma seperated values for each subscriptionID.

SubscriptionID GenerationTimes

6519    NULL
6616    NULL
6617    NULL
6618    9:00:00
6618    17:00:00
6634    NULL
6698    0:00:00

I need the result as follows

SubscriptionID GenerationTimes

6519    NULL
6616    NULL
6617    NULL
6618    9:00, 17:00
6634    NULL
6698    0:00:00

you notice for subscriptionID 6618 there are two generation times and so I have to put them in one row with comma ',' separated. Please give me suggestion how i can write the T-sql statement.

Johan
  • 74,508
  • 24
  • 191
  • 319
desi
  • 466
  • 1
  • 4
  • 22

1 Answers1

5

You can use for xml path like so:

select 
      t1.SubscriptionID,
      (select GenerationTimes + ', '
       from tableName t2
       where t1.SubscriptionID = t2.SubscriptionID
       for xml path('')) as GenerationTimes
from tableName t1
group by t1.SubscriptionID
Aducci
  • 26,101
  • 8
  • 63
  • 67
  • Thanks Aducci, It is very simple using xml path. I can straight away use this as t-sql statement instead using a seperate function to get the result. – desi Aug 30 '11 at 13:27
  • Even though the time values in this question will not include "special characters", this is not the best way to use XML for concatenation. Special XML characters like `>`, `<`, `&`, etc will be expanded to: `>`, `<`, `&`. Here is a very similar, yet better method to prevent this: http://stackoverflow.com/questions/6413627/concatenate-column-values-for-rows-with-the-same-values-of-different-columns/6413796#6413796 This isn't really an issue for the data in this particular case, but it is best to learn a method that will work on all of your data, so you won't get burned later. – KM. Aug 30 '11 at 13:44