SQL 2005 行列转换 实例（函数PIVOT&UNPIVOT）

 

SQL 行转列 实例

 

1.有表A，结构如下： A: p_ID p_Num s_id 1 10 01 1 12 02 2 8 01 3 11 01 3 8 03 其中：p_ID为产品ID，p_Num为产品库存量，s_id为仓库ID。请用SQL语句实现将上表中的数据合并，合并后的数据为： p_ID s1_id s2_id s3_id 1 10 12 0 2 8 0 0 3 11 0 8 其中：s1_id为仓库1的库存量，s2_id为仓库2的库存量，s3_id为仓库3的库存量。如果该产品在某仓库中无库存量，那么就是0代替。 Create table CREATE TABLE [dbo].[temp_anny_tab]( [p_id] [int] NULL, [p_num] [int] NULL, [s_id] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] insert value insert into temp_anny_tab(p_id,p_num,s_id) values(1,10,01) insert into temp_anny_tab(p_id,p_num,s_id) values(1,12,02) insert into temp_anny_tab(p_id,p_num,s_id) values(2,8,01) insert into temp_anny_tab(p_id,p_num,s_id) values(3,11,01) insert into temp_anny_tab(p_id,p_num,s_id) values(3,8,03) SQL 2000行转列写法: select p_id,s1_id=sum(case s_id when '01' then p_num else 0 end), s2_id=sum(case s_id when '02' then p_num else 0 end), s3_id=sum(case s_id when '03' then p_num else 0 end) from temp_anny_tab group by p_id SQL2005 行转列写法: select p_id,[01] as s1_id,[02] as s2_id,[03] as s3_id from temp_anny_tab s PIVOT (sum (p_num) FOR s_id IN ([01],[02],[03])) as pvt PIVOT和UNPIVOT关系运算符是SQL Server 2005提供的新增功能，因此，对升级到SQL Server 2005的数据库使用PIVOT和UNPIVOT时，数据库的兼容级别必须设置为90（可以使用sp_dbcmptlevel存储过程设置兼容级别）。 　　在查询的FROM子句中使用PIVOT和UNPIVOT，可以对一个输入表值表达式执行某种操作，以获得另一种形式的表。PIVOT运算符将输入表的行旋转为列，并能同时对行执行聚合运算。而UNPIVOT运算符则执行与PIVOT运算符相反的操作，它将输入表的列旋转为行。 　　在FROM子句中使用PIVOT和UNPIVOT关系运算符时的语法格式如下： [ FROM { ＜table_source＞ } [ ,...n ] ] ＜table_source＞ ::= { 　table_or_view_name [ [ AS ] table_alias ] 　＜pivoted_table＞ | ＜unpivoted_table＞ } ＜pivoted_table＞ ::=table_source PIVOT ＜pivot_clause＞ table_alias ＜pivot_clause＞ ::=( aggregate_function ( value_column ) 　FOR pivot_column 　　IN ( ＜column_list＞ ) ) ＜unpivoted_table＞ ::=table_source UNPIVOT ＜unpivot_clause＞ table_alias ＜unpivot_clause＞ ::=( value_column FOR pivot_column IN ( ＜column_list＞ ) ) ＜column_list＞ ::= column_name [ , ... ] table_source PIVOT ＜pivot_clause＞ 　　指定对table_source表中的pivot_column列进行透视。table_source可以是一个表、表表达式或子查询。 aggregate_function 　　系统或用户定义的聚合函数。注意：不允许使用COUNT(*)系统聚合函数。 value_column 　　PIVOT运算符用于进行计算的值列。与UNPIVOT一起使用时，value_column不能是输入table_source中的现有列的名称。 FOR pivot_column 　　PIVOT运算符的透视列。pivot_column必须是可隐式或显式转换为nvarchar()的类型。 　　使用UNPIVOT时，pivot_column是从table_source中提取输出的列名称，table_source中不能有该名称的现有列。 IN ( column_list ) 　　在PIVOT子句中，column_list列出pivot_column中将成为输出表的列名的值。 　　在UNPIVOT子句中，column_list列出table_source中将被提取到单个pivot_column中的所有列名。 table_alias 　　输出表的别名。 UNPIVOT ＜ unpivot_clause ＞ 　　指定将输入表中由column_list指定的多个列的值缩减为名为pivot_column的单个列。 　　常见的可能会用到PIVOT的情形是：需要生成交叉表格报表以汇总数据。交叉表是使用较为广泛的一种表格式，例如，图5-4所示的产品销售表就是一个典型的交叉表，其中的月份和产品种类都可以继续添加。但是，这种格式在进行数据表存储的时候却并不容易管理，要存储图5-4这样的表格数据，数据表通常需要 设计为图5-5这样的结构。这样就带来一个问题，用户既希望数据容易管理，又希望能够生成一种能够容易阅读的表格数据。好在PIVOT为这种转换提供了便利。   图5-4 产品销售表 图5-5 数据表结构 　　假设Sales.Orders表中包含有ProductID（产品ID）、OrderMonth（销售月份）和SubTotal（销售额）列，并存储有如表5-2所示的内容。 表5-2 Sales.Orders表中的内容 ProductID OrderMonth SubTotal 1 5 100.00 1 6 100.00 2 5 200.00 2 6 200.00 2 7 300.00 3 5 400.00 3 5 400.00 　　执行下面的语句： SELECT ProductID, [5] AS 五月, [6] AS 六月, [7] AS 七月 FROM Sales.Orders PIVOT(SUM (Orders.SubTotal) FOR Orders.OrderMonth IN ( [5], [6], [7] )) AS pvt ORDER 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 SumSubTotal FROM Sales.Orders GROUP BY ProductID,OrderMonth; 　　这时候将得到一个如表5-3所示的中间结果集。其中只有ProductID为3的产品由于在5月有2笔销售记录，被累加到了一起（值为800）。 表5-3 Sales.Orders表经分组汇总后的结果 ProductID OrderMonth SumSubTotal 1 5 100.00 1 6 100.00 2 5 200.00 2 6 200.00 2 7 300.00 3 5 800.00 　　b．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 7 1 100.00 100.00 NULL 2 200.00 200.00 200.00 3 800.00 NULL NULL 　　c．最后根据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.00 200.00 200.00 3 800.00 NULL NULL 　　UNPIVOT与PIVOT执行几乎完全相反的操作，将列转换为行。但是，UNPIVOT并不完全是PIVOT的逆操作，由于在执行PIVOT过程中，数据已经被进行了分组汇总，所以使用UNPIVOT并不会重现原始表值表达式的结果。假设表5-5所示的结果集存储在一个名为MyPvt的表中，现在需要将列标识符“五月”、“六月”和“七月”转换到对应于相应产品ID的行值（即返回到表5-3所示的格式）。这意味着必须另外标识两个列，一个用于存储月份，一个用于存储销售额。为了便于理解，仍旧分别将这两个列命名为OrderMonth和SumSubTotal。参考下面的语句：   CREATE TABLE MyPvt (ProductID int, [五月] int, [六月] int, [七月] int);  --建立MyPvt表 GO INSERT INTO MyPvt VALUES (1,100,100,0); INSERT INTO MyPvt VALUES (2,200,200,200); INSERT INTO MyPvt VALUES (3,800,0,0); SELECT * FROM MyPvt GO 　　--执行UNPIVOT SELECT ProductID, OrderMonth, SubTotal     FROM  ( SELECT ProductID,[五月], [六月], [七月] from MyPvt ) t1 UNPIVOT (SubTotal FOR OrderMonth IN ([五月],[六月],[七月]) ) AS t2    　　上面的语句将按下面的步骤获得输出结果集： 　　a．首先建立一个临时结果集的结构，该结构中包含MyPvt表中除IN (五月, 六月, 七月)之外的列，以及SubTotal FOR OrderMonth中指定的值列（SubTotal）和透视列（OrderMonth）。 　　b．将在MyPvt中逐行检索数据，将表的列名称（在IN (五月, 六月, 七月)中指定）放入OrderMonth列中，将相应的值放入到SubTotal列中。最后得到的结果集如表5-6所示。 表5-6 使用UNPIVOT得到的结果集 ProductID OrderMonth SubTotal 1 五月 100 1 六月 100 1 七月 0 2 五月 200 2 六月 200 2 七月 200 3 五月 800 3 六月 0 3 七月 0