在新表中使用pivot/unpivot的SQL服务器

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.

毫无疑问，在过去的几天里，我一直在试图理解pivot/unpivot主题，没有任何希望。我不明白什么是主元，什么是主元。我有这张桌子:这张桌子叫做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

1 个解决方案

#1

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

您需要使用PIVOT函数将数据行转换为列:

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.

参见SQL小提琴演示。UNPIVOT函数用于获取多个列并将它们转换为多行数据。

#1