Transpose data

• scotsditch

  Ten Centuries

  Points: 1020

  More actions

  June 27, 2016 at 7:56 pm

  #319594

  I have data like the "Before" snapshot below, and I would like to transpose it so it would have the form of the "After" snapshot. If anyone knows how to do this with sql I would very much appreciate the tip.

  

• Umer Akram

  SSC-Insane

  Points: 22209

  More actions

  June 28, 2016 at 5:14 am

  #1887235

  Declare @Actual Table

  (

  ItemState varchar(10)

  , Car varchar(10)

  , Truck int

  , Boats int

  , Planes int

  )

  insert into @Actual

  select 'old', 'volvo', 89, 65, 67 union all

  select 'new', 'pinto', 102, 55, 12 union all

  select 'used', 'mercedes', 3, 22, 14 union all

  select 'stolen', 'winnebago', 4, 11, 156

  ;

  select * From @Actual

  ;

  WITH CTE (ItemState, SortKey, colname, Value)

  AS

  (

  select ItemState, 1, 'cars', cast(car as varchar(500))

  from @Actual

  UNION ALL

  select ItemState, 2, 'Truck', cast(Truck as varchar(500))

  from @Actual

  UNION ALL

  select ItemState, 3, 'Boats', cast(Boats as varchar(500))

  from @Actual

  UNION ALL

  select ItemState, 4, 'Planes', cast(Planes as varchar(500))

  from @Actual

  )

  select t2.colname, [old], [new], [used],[stolen]

  from

  (

  select c.SortKey, c.ItemState, c.colname, c.Value

  from cte c

  ) t1

  Pivot

  (

  max(value) for t1.ItemState IN ([old], [new], [used],[stolen])

  ) as t2

  order by t2.SortKey

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  June 28, 2016 at 5:47 am

  #1887236

  SELECT

  NewColumn,

  [Old] = MAX(CASE WHEN ItemState = 'old' THEN [Stuff] ELSE NULL END),

  [New] = MAX(CASE WHEN ItemState = 'new' THEN [Stuff] ELSE NULL END),

  [Used] = MAX(CASE WHEN ItemState = 'used' THEN [Stuff] ELSE NULL END),

  [stolen] = MAX(CASE WHEN ItemState = 'stolen' THEN [Stuff] ELSE NULL END)

  FROM @Actual

  CROSS APPLY (VALUES

  ('Cars', car),

  ('Trucks', CAST(Truck AS VARCHAR(10))),

  ('Boats', CAST(Boats AS VARCHAR(10))),

  ('Planes', CAST(Planes AS VARCHAR(10)))

  ) x (NewColumn, [stuff])

  GROUP BY x.NewColumn

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden