How to select comma separated columns data

• purushottam2

  SSCrazy

  Points: 2021

  More actions

  January 30, 2013 at 3:31 am

  #269846

  How to select comma separated columns data, i was using below query but due to large data it is killing performance.

  select id, (select itbl.name+','

  from tblname itbl

  where itbl.id=tbl.id

  for xml path('')) name

  from tblname tbl

  group by id

  I am looking for any alternative.

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  January 30, 2013 at 3:49 am

  #1582542

  this is not actual query (not the actual table too) . Anyways Do you have index on ID column here ?

  and do you need complete table in this query ? because here it will go to scan (table/clustered)

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

• subbu1

  Hall of Fame

  Points: 3281

  More actions

  January 30, 2013 at 4:58 am

  #1582552

  check ...

  Do you have index on ID column ?

  if there is no index on id column then create non cluster index on id column

  after that

  try below code.

  DECLARE @T1 TABLE(ID INT,dept varchar(10))

  INSERT INTO @T1(ID,dept)VALUES(1,'A'),(1,'B') ,(1,'C'),(2,'A'),(2,'B')

  select DISTINCT id, (select T1.dept+','

  from @T1 T1

  where T1.id=T2.id

  for xml path('')) name

  from @T1 T2