Different Query Requirement

• sqlnaive

  SSCoach

  Points: 17437

  More actions

  July 4, 2011 at 4:24 am

  #96414

  I have one query which gives result as

  Col1Col2Col3Col4

  ABCXXX11221

  ABCXXX12121

  ABCXXX18232

  CBZLLL21331

  CBZLLL22131

  CBZLLL21561

  I further need to process it and find result as

  Col1Col2Col3Col4

  ABCXXX1122,12121

  ABCXXX18232

  CBZLLL2133,2213,21561

• dave.repton

  SSCrazy

  Points: 2468

  More actions

  July 4, 2011 at 4:36 am

  #1347727

  Maybe not the most efficient way, but you can achieve this using recursive ctes

  declare @data table(Col1 varchar(5), Col2 varchar(5), Col3 varchar(5), Col4 varchar(5))

  insert @data

  select 'ABC', 'XXX', '1122', '1'

  union all

  select 'ABC', 'XXX', '1212', '1'

  union all

  select 'ABC', 'XXX', '1823', '2'

  union all

  select 'CBZ', 'LLL', '2133', '1'

  union all

  select 'CBZ', 'LLL', '2213', '1'

  union all

  select 'CBZ', 'LLL', '2156', '1'

  ;with cte1 as (

  select col1

  ,col2

  ,col3

  ,col4

  ,row_number() over (partition by col1, col2, col4 order by col3) seq

  from @data

  )

  ,cte2 as (

  select Col1

  ,Col2

  ,cast(Col3 as varchar(max)) Col3

  ,Col4

  ,Seq

  from cte1

  where seq = 1

  union all

  select a.Col1

  ,a.Col2

  ,b.col3 + ',' + a.col3

  ,a.Col4

  ,a.seq

  from cte1 a

  inner join cte2 b

  on a.col1 = b.col1

  and a.col2 = b.col2

  and a.col4 = b.col4

  and a.seq = b.seq +1

  )

  ,cte3 as (

  select col1

  ,col2

  ,col4

  ,max(seq) seq

  from cte2

  group by col1, col2, col4

  )

  select b.Col1

  ,b.col2

  ,b.col3

  ,b.col4

  from cte3 a

  inner join cte2 b

  on a.col1 = b.col1

  and a.col2 = b.col2

  and a.col4 = b.col4

  and a.seq = b.seq

  order by col1, col2, col4

• sqlnaive

  SSCoach

  Points: 17437

  More actions

  July 5, 2011 at 12:55 am

  #1348075

  Thanks Dave. That was quick and helpful. I will check if that can be optimized but still that was of great help.

• LutzM

  SSC Guru

  Points: 107049

  More actions

  July 5, 2011 at 1:07 am

  #1348085

  Here's a slightly different approach:

  select

  col1,

  col2,

  stuff(

  (select ',' + col3

  from @data d2

  where d2.col1=d1.col1 and d2.col2=d1.col2 and d2.col4=d1.col4

  order by col3

  for xml path('')

  ),1,1,'') col3,

  col4

  from @data d1

  group by col1, col2,col4

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]