CODEWITHSUNDEEP
sqlsql-serverpivot
Roland P
Roland P

Reputation: 403

How to pivot 2 columns into one row

I am trying to pivot a table that has both Units and Base Units for a certain product. I am trying to make my end result one line only.

 Product   Unit     BaseUnit
 Gas       MMcf       Mcf
 Oil       Mbbl       bbl
 Water     Mgal       gal

My output table should look like this:

GasUnit  GasBaseUnit OilUnit OilBaseUnit WaterUnit WaterBaseUnit
 MMcf       Mcf       Mbbl       bbl       Mgal        gal

I have done a pivot table before but never had to pivot 2 columns at the same time and move them into one row.

Here is what I have so far. Can I use 2 aggregates? I am not sure how to approach this.

SELECT *
from
(
    select Product, Unit, BaseUnit
    from t
) x
pivot
(
    sum(BaseUnit)
    for Product in ([Gas], [Oil], [Water])
) p

Upvotes: 1

Views: 1799

Answers (1)

Serpiton
Serpiton

Reputation: 3684

With static data this will get your output table

WITH prep AS (
  SELECT Product + ' Unit' TYPE, Unit Value
  FROM   utilities
  UNION ALL
  SELECT Product + ' BaseUnit' TYPE, BaseUnit Value
  FROM   utilities
)
SELECT [Gas Unit], [Gas BaseUnit]
     , [Oil Unit], [Oil BaseUnit]
     , [Water Unit], [Water BaseUnit]
FROM   (SELECT TYPE, Value
        FROM   prep
       ) a
       PIVOT
       (MAX(Value)
        FOR TYPE IN ([Gas Unit], [Gas BaseUnit]
                   , [Oil Unit], [Oil BaseUnit]
                   , [Water Unit], [Water BaseUnit])
       ) pvt

Demo: SQLFiddle

With dynamic data you'll need to use a dynamic query to create the PIVOT columns list

Upvotes: 1

Related Questions