Pivot help please?

• kiril.lazarov.77

  Hall of Fame

  Points: 3742

  More actions

  October 15, 2015 at 2:05 pm

  #312330

  I could pivot salesprice but not both salesprice and quantity. Is it possible? thanks

  -- sample data

  declare @t table (category varchar(100), salesprice money, quantity int)

  insert into @t

  select 'cat1', 654.00, 56 union all

  select 'cat2', 458.00, 45 union all

  select 'cat3', 789.00, 24 union all

  select 'cat4', 666.00, 77 union all

  select 'cat5', 1545.00, 69 union all

  select 'cat6', 888.00, 99 union all

  select 'cat7', 45.00, 12 union all

  select 'cat8', 544.00, 30

  select * from @t

  -- expected output

  select

  654.00 as cat1_salesprice

  , 56 as cat1_quantity

  , 458.00 as cat2_salesprice

  , 45 as cat2_quantity

  , 789.00 as cat3_salesprice

  , 24 as cat3_quantity

  , 666.00 as cat4_salesprice

  , 77 as cat4_quantity

  , 1545.00 as cat5_salesprice

  , 69 as cat5_quantity

  , 888.00 as cat6_salesprice

  , 99 as cat6_quantity

  , 45.00 as cat7_salesprice

  , 12 as cat7_quantity

  , 544.00 as cat8_salesprice

  , 30 as cat8_quantity

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  October 15, 2015 at 2:14 pm

  #1833613

  Quick option: Use cross tabs.

  select

  SUM( CASE WHEN category = 'cat1' THEN salesprice ELSE 0 END) AS cat1_salesprice,

  SUM( CASE WHEN category = 'cat1' THEN quantity ELSE 0 END) AS cat1_quantity,

  SUM( CASE WHEN category = 'cat2' THEN salesprice ELSE 0 END) AS cat2_salesprice,

  SUM( CASE WHEN category = 'cat2' THEN quantity ELSE 0 END) AS cat2_quantity,

  SUM( CASE WHEN category = 'cat3' THEN salesprice ELSE 0 END) AS cat3_salesprice,

  SUM( CASE WHEN category = 'cat3' THEN quantity ELSE 0 END) AS cat3_quantity,

  SUM( CASE WHEN category = 'cat4' THEN salesprice ELSE 0 END) AS cat4_salesprice,

  SUM( CASE WHEN category = 'cat4' THEN quantity ELSE 0 END) AS cat4_quantity,

  SUM( CASE WHEN category = 'cat5' THEN salesprice ELSE 0 END) AS cat5_salesprice,

  SUM( CASE WHEN category = 'cat5' THEN quantity ELSE 0 END) AS cat5_quantity

  --...

  from @t

  The other option is to use a double pivot, but I wouldn't use it myself as it will be slower and more complicated.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• drew.allen

  SSC Guru

  Points: 77009

  More actions

  October 15, 2015 at 2:58 pm

  #1833634

  kiril.lazarov.77 (10/15/2015)

  ---

  I could pivot salesprice but not both salesprice and quantity. Is it possible? thanks

  Not with one pivot, but there is nothing to stop you from using two pivots. That being said, I agree that the crosstab is the better approach.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• kiril.lazarov.77

  Hall of Fame

  Points: 3742

  More actions

  October 16, 2015 at 10:50 pm

  #1833931

  Thank you very much!