CODEWITHSUNDEEP
sqlsql-servert-sql
Gusti Arya
Gusti Arya

Reputation: 1301

sql server create pivot from two columns

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

Upvotes: 0

Views: 69

Answers (1)

LukStorms
LukStorms

Reputation: 29647

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

Upvotes: 3

Related Questions