[SOLVED] SQL Server using pivot/unpivot in a new table

SQL Server using pivot/unpivot in a new table

Needless to say, I am trying to understand pivot/unpivot topic in the last few days with no hope. I do not understand what is pivot and what is unpivot. I have this table: This table is called Natalie_Playground.

BuildingName    BillingMonth    Consumption

Building1       1/1/2011        59318

Building2       1/1/2011        6962

Building3       1/1/2011        204300

Building4       1/1/2011        69600

Building5       2/1/2011        47316

Building6       2/1/2011        162300

Building7       2/1/2011        7122

Building8       2/1/2011        7444

I do not know if I have to use pivot or unpivot to make my table looks like this:

BuildingName    January      February    March  .... December

Building1       59318        47316

Building2       6962         162300

Building3       204300       162300

Building4       69600        7444

Solution

You will want to use the PIVOT function to convert the rows of data into columns:

select buildingname, January, February, March, April
from
(
  select buildingname,
    datename(month, billingmonth) month,
    consumption
  from yourtable
) d
pivot
(
  sum(consumption)
  for month in (January, February, March, April)
) piv;

See SQL Fiddle with Demo. The UNPIVOT function is used to take multiple columns and convert them into multiple rows of data.