图5-4 产品销售表 图5-5 数据表结构
假设Sales.Orders表中包含有ProductID(产品ID)、OrderMonth(销售月份)和SubTotal(销售额)列,并存储有如表5-2所示的内容。
表5-2 Sales.Orders表中的内容
ProductID OrderMonth SubTotal 15 100.00 1 6 100.00 2 5 200.00 26 200.00 2 7 300.00 35400.00 35400.00执行下面的语句:
SELECT ProductID, [5] AS 五月, [6] AS 六月, [7] AS 七月FROMSales.Orders PIVOT(SUM (Orders.SubTotal)FOR Orders.OrderMonth IN( [5], [6], [7] )) AS pvtORDER BY ProductID;
在上面的语句中,Sales.Orders是输入表,Orders.OrderMonth是透视列(pivot_column),Orders.SubTotal是值列(value_column)。上面的语句将按下面的步骤获得输出结果集:
a.PIVOT首先按值列之外的列(ProductID和OrderMonth)对输入表Sales.Orders进行分组汇总,类似执行下面的语句:
SELECT ProductID,OrderMonth,SUM (Orders.SubTotal) AS SumSubTotalFROM Sales.OrdersGROUP BY ProductID,OrderMonth;
这时候将得到一个如表5-3所示的中间结果集。其中只有ProductID为3的产品由于在5月有2笔销售记录,被累加到了一起(值为800)。
表5-3 Sales.Orders表经分组汇总后的结果
ProductIDOrderMonthSumSubTotal 1 5 100.00 1 6 100.00 2 5 200.00 2 6 200.00 27300.00 35800.00b.PIVOT根据FOR Orders.OrderMonth IN指定的值5、6、7,首先在结果集中建立名为5、6、7的列,然后从图5-3所示的中间结果中取出OrderMonth列中取出相符合的值,分别放置到5、6、7的列中。此时得到的结果集的别名为pvt(见语句中AS pvt的指定)。结果集的内容如表5-4所示。
表5-4 使用FOR Orders.OrderMonth IN( [5], [6], [7] )后得到的结果集
ProductID 5 6 71 100.00 100.00 NULL2 200.00 200.00200.003 800.00NULLNULLc.最后根据SELECT ProductID, [5] AS 五月, [6] AS 六月, [7] AS 七月FROM的指定,从别名pvt结果集中检索数据,并分别将名为5、6、7的列在最终结果集中重新命名为五月、六月、七月。这里需要注意的是FROM的含义,其表示从经PIVOT关系运算符得到的pvt结果集中检索数据,而不是从Sales.Orders中检索数据。最终得到的结果集如表5-5所示。
表5-5 由表5-2所示的Sales.Orders表将行转换为列得到的最终结果集
ProductID五月六月七月 1 100.00 100.00 NULL 2 200.00200.00200.00 3 800.00 NULL NULLUNPIVOT与PIVOT执行几乎完全相反的操作,将列转换为行。但是,UNPIVOT并不完全是PIVOT的逆操作,由于在执行PIVOT过程中,数据已经被进行了分组汇总,所以使用UNPIVOT并不会重现原始表值表达式的结果。假设表5-5所示的结果集存储在一个名为MyPvt的表中,现在需要将列标识符五月、六月和七月转换到对应于相应产品ID的行值(即返回到表5-3所示的格式)。这意味着必须另外标识两个列,一个用于存储月份,一个用于存储销售额。为了便于理解,仍旧分别将这两个列命名为OrderMonth和SumSubTotal。参考下面的语句:
(本文来源于图老师网站,更多请访问http://m.tulaoshi.com/bianchengyuyan/)
CREATE TABLE MyPvt (ProductID int, 五月int, 六月 int, 七月int); --建立MyPvt表GO --将表5-5中所示的值插入到MyPvt表中INSERT INTO MyPvt VALUES (1,100,100,0);INSERT INTO MyPvt VALUES (2,200,200,200);INSERT INTO MyPvt VALUES (3,800,0,0); --执行UNPIVOTSELECT ProductID, OrderMonth, SubTotalFROM MyPvt UNPIVOT (SubTotal FOR OrderMonth IN (五月, 六月, 七月) )AS unpvt;(本文来源于图老师网站,更多请访问http://m.tulaoshi.com/bianchengyuyan/)