PIvot with multiple result values

Question

im trying to put multiple sums in this pivot table but i havent been succeful,

This is the Raw data from

select 
    billname, 
    orderperiodyear,
    Sum_buyprice,
    Sum_Sellprice,
    Tonnage 
From Sum_Orders

This is my pivot how can i include buy price and tonnage in the pivot results

SELECT billname,SUM([2017])AS '2017', SUM([2016]) AS '2016'
FROM   Sum_Orders
PIVOT
    (
       SUM(Sum_SellPrice)
            FOR OrderperiodYear IN ([2017],[2016])
     )AS pvt
WHERE  OrderStatus IN ('Complete', 'Invoiced') 
  AND ( (MONTH(OrderDate) = MONTH(GETDATE()) AND day(OrderDate) <= DAY(GETDATE()))
   OR MONTH(OrderDate) < MONTH(GETDATE()))

Group by BILLNAME
HAVING COALESCE(SUM([2017]), SUM([2016])) IS NOT NULL
ORDER BY BILLNAME ASC

This is what i get from my pivot

Im looking for something like this

[why-may-i-not-upload-images-of-code](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) — Juan Carlos Oropeza, Feb 21 '17 at 20:12
You have to do unpivot first, and instead of `year` you create values like `year_sell` check this [**example**](http://stackoverflow.com/a/42354610/3470178) to get a base query. — Juan Carlos Oropeza, Feb 21 '17 at 20:50

score 2 · Answer 1 · edited May 23 '17 at 12:00

2

You first have to do unpivot first DEMO

SELECT billname, 
       CAST([OrderperiodYear] as varchar(500)) + '_' + CAST([attribute] as varchar(500)) as attribute,
       [data]
FROM (SELECT billname, 
             [OrderperiodYear],
             [Sum_Buyprice] as Buy,
             [Sum_Sellprice] as Sell,
             [Tonnage] as Ton
      FROM records) p
UNPIVOT 
    ([data] FOR [attribute] IN 
                (Buy, Sell, Ton)
     ) as unpvt

OUTPUT

Then you can create a Dynamic Pivot.

edited May 23 '17 at 12:00

Community

1
1

answered Feb 21 '17 at 21:14

Juan Carlos Oropeza

47,252
12
78
118

There is an issue with this sample when the value are null. You can solve by using coalesce and convert those to a negative number like -1. http://rextester.com/ZOO68838 or checking any of this [**version**](http://stackoverflow.com/questions/1002989/sql-server-include-null-using-unpivot). – Juan Carlos Oropeza Feb 21 '17 at 21:30

SqlZim · Accepted Answer · 2017-02-21T21:26:34.383

One option is to skip pivot() and go for the old style cross tab.

select 
    billname
  , [2016_buyprice]  = sum(case when OrderPeriodYear = 2016 then sum_buyprice else null end)
  , [2017_buyprice]  = sum(case when OrderPeriodYear = 2017 then sum_buyprice else null end)
  , [2016_sellprice] = sum(case when OrderPeriodYear = 2016 then sum_sellprice else null end)
  , [2017_sellprice] = sum(case when OrderPeriodYear = 2017 then sum_sellprice else null end)
  , [2016_tonnage]   = sum(case when OrderPeriodYear = 2016 then tonnage else null end)
  , [2017_tonnage]   = sum(case when OrderPeriodYear = 2017 then tonnage else null end)
from sum_orders 
where OrderStatus in ('Complete', 'Invoiced') 
  and ((month(OrderDate) = month(getdate()) 
    and day(OrderDate) <= day(getdate()))
    or month(OrderDate) < month(getdate()))
group by billname
order by billname asc

This also will give you an idea on how solve it with pivot. instead of try multi level columns like `{2017, {buy,sell,tonnage}}` you create columns `{2017_buy, 2017_sell, 2017_tonnage}` — Juan Carlos Oropeza, Feb 21 '17 at 20:18

PIvot with multiple result values

2 Answers2