How to chnage the data display format column to row

• vijay_uitrgpv

  SSC-Addicted

  Points: 405

  More actions

  November 21, 2017 at 1:36 am

  #402685

  I have data in below format and would like to generate in attached format

  
Declare @TowerSDGoup as table ( SDgroup varchar(20), Tower varchar(20))

  Insert into @TowerSDGoup ( SDgroup, Tower)
Select 'SD1', 'Tower1'
union all
Select 'SD2', 'Tower1'
union all
Select 'SD3', 'Tower2'
union all
Select 'SD4', 'Tower2'
  
  

  
  

• Thom A

  SSC Guru

  Points: 99126

  More actions

  November 21, 2017 at 1:55 am

  #1968956

  One method would be by using STUFF and FOR XML PATH:

  SELECT T.Tower,

     STUFF((SELECT ',' + sq.SDgroup

       FROM @TowerSDGoup sq

       WHERE sq.Tower= T.Tower

       ORDER BY sq.SDgroup

      FOR XML PATH ('')),1,1,'') AS SDGroup

  FROM @TowerSDGoup T

  GROUP BY T.Tower;

  Let me know if you have any questions.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• vijay_uitrgpv

  SSC-Addicted

  Points: 405

  More actions

  November 21, 2017 at 3:07 am

  #1968961

  Thanks Thom for shortest answer..
  Though I was trying my hand on this
  Declare @T varchar(20)
Declare @SD varchar(20)
Declare @STR varchar(100)

  Declare @TowerSDGoup as table ( SDgroup varchar(20), Tower varchar(20))

  Insert into @TowerSDGoup ( SDgroup, Tower)
  Select 'SD1', 'Tower1'
  union all
  Select 'SD2', 'Tower1'
  union all
  Select 'SD3', 'Tower2'
  union all
  Select 'SD4', 'Tower2'
  union all
  Select 'SD5', 'Tower2'

  Declare @Tower as Table ( Tower varchar(20))

  Insert into @Tower
  Select distinct Tower from @TowerSDGoup

  Declare @TSDg as Table (SD varchar(20), T varchar(20))

  Declare @FinalTSDg as Table ( T varchar(50), SD varchar(100))

  While (Select top 1 1 from @Tower ) >0
  BEGIN
  SET @STR  =''
   Select top 1 @T = Tower from @Tower
   
   
    Insert into @TSDg( SD, T )
    SElect SDgroup, Tower FROM @TowerSDGoup WHERE Tower =@T

  
     While ( Select top 1 1 from @TSDg)>0
      BEGIN
     
     Select top 1 @SD = SD from @TSDg where T =@T

     SET  @STR = @STR + ','+@SD
    

     Delete from @TSDg WHere SD = @SD
    
      Insert into @FinalTSDg (T , SD)
     Select @T,@str
      END 
   

     Delete from @Tower Where Tower =@T
  END

  
  Select Tower,SD from (
  Select row_number() over ( Partition By T order by SD desc)rn,t as Tower, SUBSTRINg(SD,2,len(SD)) AS SD from @FinalTSDg )t
  where rn = 1

• Thom A

  SSC Guru

  Points: 99126

  More actions

  November 21, 2017 at 3:14 am

  #1968962

  I woulnd't recommend a WHILE loop. It'll be far slower, due to it's inherent repetitive nature. 🙂

  When using SQL, try to think in Datasets, rather than programmatically. SQL is far faster at performing changes, evaluating expressions, etc, at a dataset level than it is at doing it Row By Agonising Row (coined as RBAR).

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• vijay_uitrgpv

  SSC-Addicted

  Points: 405

  More actions

  November 21, 2017 at 3:56 am

  #1968967

  Thanks for your guidance