Post reply

Pivot help please?

  • 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

  • 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

  • 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

  • October 16, 2015 at 10:50 pm

    #1833931

    Thank you very much!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply