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: 99143

  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: 99143

  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