sql server create pivot from two columns

Question

I have this table

|---------------------|------------------|----------|--------|
|      Period         |     Calorie      |Tonnage   |product |
|---------------------|------------------|----------|--------|
|     2020-01-01      |       4989       |110000    |E5000   |
|---------------------|------------------|----------|--------|
|     2020-02-01      |       5200       |120000    |E5000   |
|---------------------|------------------|----------|--------|
|     2020-03-01      |       7000       |130000    |E5000   |
|---------------------|------------------|----------|--------|

and I the expected output is like this

|---------------------|------------------|----------|--------|
|      Info           |     Jan          |Feb       |March   |
|---------------------|------------------|----------|--------|
|     E5000           |       110000     |120000    |130000  |
|---------------------|------------------|----------|--------|
|     Calorie         |       4989       |5200      |7000    |
|---------------------|------------------|----------|--------|

I am able to flat out single column using pivot, but I don't know how to pivot more than one column and produce result like expected above. below is my current query

select  * from (
select
    a.[Product], 
    a.Tonnage,
    LEFT( DATENAME(MONTH, [Period]) ,3) as MLM 
   from 
    [ProductionData] a 
   Where 
    DATEPART(YEAR,a.[Period]) = 2019) PS
   PIVOT 
    (AVG (Tonnage) for MLM in ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])) PVT

please advice, Thanks

you can refer : [sql server dynamic pivot](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Barbaros Özhan, Aug 23 '19 at 07:52
There is no such think as "row below the row" in the sql world. You need an explicit `order by ` to have rows odered. And i see no way to order the result as it is. Consider a result schema `Product, Info,Jan,Feb,March` where `Info` values in `Calorie,Tonnage `. — Serg, Aug 23 '19 at 11:56

LukStorms · Answer 1 · 2021-10-18T08:28:03.793

One way is simply a UNION ALL of two queries in the source query.

SELECT * 
FROM (
   SELECT
    [Product] AS Info,
    LEFT( DATENAME(MONTH, [Period]) ,3) AS MLM,
    Tonnage AS Val
   FROM [ProductionData]
   WHERE DATEPART(YEAR, [Period]) = 2019

   UNION ALL

   SELECT
    'Calorie' AS Info,
    LEFT(DATENAME(MONTH, [Period]) ,3) AS MLM,
    Calorie AS Val
   FROM [ProductionData]
   WHERE DATEPART(YEAR, [Period]) = 2019
) SRC
PIVOT (
   AVG (Val) 
   FOR MLM in ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])
) PVT

Or replace such UNION ALL by a CROSS APPLY to values.

SELECT * 
FROM (
   SELECT
    ca.Info,
    LEFT(DATENAME(MONTH, t.[Period]) ,3) AS MLM,
    ca.Val
   FROM [ProductionData] t
   CROSS APPLY(VALUES (t.[Product], t.Tonnage),('Calorie', t.Calorie)) ca(Info, Val)
   WHERE DATEPART(YEAR, t.[Period]) = 2019
) SRC
PIVOT (
   AVG (Val) 
   FOR MLM in ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])
) PVT

sql server create pivot from two columns

1 Answers1