Tuesday, September 1, 2009

Concatenate multiple rows delimited by comma

Hello frnds,
Most of people need to select multiple row and convert it into CSV format.
Here I came with solutions.

For Example:-


   1:  Declare @tbl table (id int,sid int)

   2:   

   3:  insert into @tbl

   4:  select 1,11

   5:  union

   6:  select 1,12

   7:  union

   8:  select 1,13

   9:  union

  10:  select 2,21

  11:  union

  12:  select 2,22

  13:   

  14:  SELECT * FROM @tbl

  15:   

  16:  --With CTE:-

  17:  --------------------

  18:  ;WITH RowRank

  19:  AS

  20:  (

  21:  SELECT id,CAST(sid AS VARCHAR) sid,ROW_NUMBER() OVER(PARTITION BY id order by id) AS Row FROM @tbl

  22:  )

  23:  ,Concat(id,sid,Row)

  24:  AS

  25:  (

  26:  SELECT id,CAST(RowRank.sid AS VARCHAR),Row FROM RowRank WHERE Row=1

  27:  UNION ALL

  28:  select RowRank.id, CAST(Concat.sid + ', ' + RowRank.sid AS VARCHAR ) ,RowRank.Row

  29:  from RowRank

  30:  inner join Concat on RowRank.id = Concat.id and RowRank.Row -1 = Concat.Row

  31:   

  32:  )

  33:  ,CommaSeperatedResult (id, sid)

  34:  as

  35:  (

  36:  select Concat.id, Concat.sid

  37:  from Concat

  38:  join (select id, max(Row) max_row from RowRank group by id) max_row

  39:  on Concat.id = max_row.id and Concat.Row = max_row.max_row

  40:  )

  41:  SELECT * FROM CommaSeperatedResult ORDER BY ID

  42:   

  43:   

  44:  --WITH FOR XML PATH:-

  45:  ------------------------------

  46:  select distinct id,

  47:  replace

  48:  (

  49:  (

  50:  select cast(sid as varchar)+',' from @tbl t where t.id=t1.id for XML path('')

  51:  ) +'$',',$',''

  52:  )

  53:  as sid

  54:  from @tbl t1

  55:   



Output is:-
----------------

No comments: