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
  • 73,011
  • 23
  • 185
  • 311
desi
  • 468
  • 1
  • 4
  • 20

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
  • 25,111
  • 8
  • 64
  • 65
  • 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 `>`, ` – KM. Aug 30 '11 at 13:44