135-1821-9792

SQLServer2005新功能之PIVOT的描述

以下的文章主要描述的是SQL Server 2005新功能之PIVOT，在工具的升级中，我个人认为首先我们的看看这个工具，其主要是在哪些功能上得到加强，所以今天我们就来看看SQL2005这个PIVOT吧。PIVOT 关系运算符对表值表达式进行操作以获得另一个表。

创新互联建站专业IDC数据服务器托管提供商，专业提供成都服务器托管，服务器租用，绵阳主机托管，绵阳主机托管，成都多线服务器托管等服务器托管服务。

PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来转换表。工具的升级，我以为得先看看这个工具在哪些功能上得到加强，今天我们就看看SQL2005这个PIVOT吧。PIVOT 关系运算符对表值表达式进行操作以获得另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来转换表值表达式，并在必要时对最终输出中所需的任何其余的列值执行聚合。

记得我们在SQL2000中要用聚合和CASE语句完成一个行列转换吧，特别当待转成列的数据不定时，我们往往构造动态SQL，然后用EXEC来运行。

环境准备：

 
 
 
    
  
  
  
  1. -- Author: happyflsytone  
    2. 
  
  
  
  2. -- Version:V1.001  
    3. 
  
  
  
  3. -- Date:2008-09-18 10:20:53  
    4. 
  
  
  
  4. -- Test Data: ta  
    5. 
  
  
  
  5. IF OBJECT_ID('ta') IS NOT NULL  
    6. 
  
  
  
  6. DROP TABLE ta  
    7. 
  
  
  
  7. ;  
    8. 
  
  
  
  8. CREATE TABLE ta(id INT,col1 Nvarchar(2),col2 Nvarchar(2),col3 Nvarchar(4),col4 INT)  
    9. 
  
  
  
  9. ;  
    10. 
  
  
  
  10. INSERT INTO ta  
    11. 
  
  
  
  11. SELECT 1,'HN','CS','abc',1 UNION ALL  
    12. 
  
  
  
  12. SELECT 2,'HN','CS','abcd',2 UNION ALL  
    13. 
  
  
  
  13. SELECT 3,'HN','CD','abcd' ,3UNION ALL  
    14. 
  
  
  
  14. SELECT 4,'HN','HY','ae' ,4  
    15. 
  
  
  
  15. ;  
    16.

我们先来回顾SQL2000的行列转换,比如我们对上例程把col3转列显示，并把col4的和当对应列值。我们分两种情况来讨论：

一、当col3的列值固定就是'abc','abcd','ae'三种情况

 
 
 
    
  
  
  
  1. SELECT  
    2. 
  
  
  
  2. col1,  
    3. 
  
  
  
  3. col2,  
    4. 
  
  
  
  4. [abc] = SUM(CASE WHEN col3 = 'abc' THEN col4 ELSE 0 END),  
    5. 
  
  
  
  5. [abcd] = SUM(CASE WHEN col3 = 'abcd' THEN col4 ELSE 0 END),  
    6. 
  
  
  
  6. [ae] = SUM(CASE WHEN col3 = 'ae' THEN col4 ELSE 0 END)  
    7. 
  
  
  
  7. FROM ta  
    8. 
  
  
  
  8. GROUP BY col1,col2  
    9. 
  
  
  
  9. /*  
    10. 
  
  
  
  10. col1 col2 abc abcd ae  
    11. 
  
  
  
  11. HN CD 0 3 0  
    12. 
  
  
  
  12. HN CS 1 2 0  
    13. 
  
  
  
  13. HN HY 0 0 4 
    14.

(3 行受影响)

二、当col3的列值不固定时就运用动态SQL，其实也就是构造一个sum(CASE WHEN ...)SQL字符串

 
 
 
    
  
  
  
  1. DECLARE @s varchar(8000)  
    2. 
  
  
  
  2. SELECT @s = isnull(@s+',  
    3. 
  
  
  
  3. ','') +'['+col3+'] = SUM(CASE WHEN col3 = '''+col3+''' THEN col4 ELSE 0 END)'  
    4. 
  
  
  
  4. FROM ( SELECT distinct col3 FROM ta) a  
    5. 
  
  
  
  5. SET @s = 'SELECT  
    6. 
  
  
  
  6. col1,  
    7. 
  
  
  
  7. col2,  
    8. 
  
  
  
  8. '+@s + '  
    9. 
  
  
  
  9. FROM ta  
    10. 
  
  
  
  10. GROUP BY  
    11. 
  
  
  
  11. col1,col2'  
    12. 
  
  
  
  12. EXEC(@s)  
    13. 
  
  
  
  13. /*  
    14. 
  
  
  
  14. col1 col2 abc abcd ae  
    15. 
  
  
  
  15. HN CD 0 3 0  
    16. 
  
  
  
  16. HN CS 1 2 0  
    17. 
  
  
  
  17. HN HY 0 0 4 
    18.

(3 行受影响)

我们先输入这个@S看看是什么东东，只要加上print @s

 
 
 
    
  
  
  
  1. SELECT  
    2. 
  
  
  
  2. col1,  
    3. 
  
  
  
  3. col2,  
    4. 
  
  
  
  4. [abc] = SUM(CASE WHEN col3 = 'abc' THEN col4 ELSE 0 END),  
    5. 
  
  
  
  5. [abcd] = SUM(CASE WHEN col3 = 'abcd' THEN col4 ELSE 0 END),  
    6. 
  
  
  
  6. [ae] = SUM(CASE WHEN col3 = 'ae' THEN col4 ELSE 0 END)  
    7. 
  
  
  
  7. FROM ta  
    8. 
  
  
  
  8. GROUP BY  
    9. 
  
  
  
  9. col1,col2 
    10.

其实就是上面我们构造的固定列值的SQL嘛。

好，现在们开始在2005中实现这个功能，先来看看2005的FROM子句的定义(关于如何看这个定义请参照SQL2005的文档约定及Transate-SQL语法约定)：

 
 
 
    
  
  
  
  1. [ FROM {  } [ ,...n ] ]  
    2. 
  
  
  
  2.  ::=  
    3. 
  
  
  
  3. {  
    4. 
  
  
  
  4.   
    5. 
  
  
  
  5. }  
    6. 
  
  
  
  6.  ::=  
    7. 
  
  
  
  7. table_source PIVOT  table_alias  
    8. 
  
  
  
  8.  ::=  
    9. 
  
  
  
  9. ( aggregate_function ( value_column )  
    10. 
  
  
  
  10. FOR pivot_column  
    11. 
  
  
  
  11. IN (  )  
    12. 
  
  
  
  12. )  
    13. 
  
  
  
  13.  ::=  
    14. 
  
  
  
  14. column_name [ , ... ]  
    15.

pivot_column 和 value_column 是 PIVOT 运算符使用的组合列。PIVOT 遵循以下过程获得输出结果集：

对分组列的 input_table 执行 GROUP BY，为每个组生成一个输出行。

输出行中的分组列获得 input_table 中该组的对应列值。

通过执行以下操作，为每个输出行生成列列表中的列的值：

针对 pivot_column，对上一步在 GROUP BY 中生成的行另外进行分组。

对于 column_list 中的每个输出列，选择满足以下条件的子组：

 
 
 
    
  
  
  
  1. pivot_column = CONVERT(, 'output_column') 
    2.

针对此子组上的 aggregate_function 对 value_column 求值，其结果作为相应的 output_column 的值返回。如果该子组为空，SQL Server 2005 将为该 output_column 生成空值。如果聚合函数是 COUNT，且子组为空，则返回零 (0)。

接着我们利用我们开头的例子来理解一下这个FROM子句，很显然我们的col4对应上面的value_column,我们还假定列会下固定为这三项，那么列 col3 对应上面的pivot_column,进而我们应该得出[abc],[abcd],[ae]是column_name即我们的输出列，最后我们只要构造一下table_source就可以了，如何构造这个table_source，显然pivot_column 和 value_column应该包含在其中，其它就应该是你想要分组的列啦.

我们来总结一下：这个FROM子句是基于 table_source 对 pivot_column 进行透视，table_source 中 pivot_column 和 value_column 列之外的列被称为透视运算符的组合列,而PIVOT 是对输入表执行组合列的分组操作，并为每个组返回一行，好，我们试着写出这个SQL：

 
 
 
    
  
  
  
  1. SELECT col1,col2,[abc],[abcd],[ae]  
    2. 
  
  
  
  2. FROM  
    3. 
  
  
  
  3. (SELECT col1,col2,col3,col4  
    4. 
  
  
  
  4. FROM ta ) p  
    5. 
  
  
  
  5. PIVOT  
    6. 
  
  
  
  6. ( SUM (col4)  
    7. 
  
  
  
  7. FOR col3 IN ([abc],[abcd],[ae])  
    8. 
  
  
  
  8. )AS unpvt 
    9.

我们执行一下看看结果：

 
 
 
    
  
  
  
  1. /*  
    2. 
  
  
  
  2. col1 abc abcd ae 
    3.

以上的相关内容就是对SQL Server 2005 的新功能的介绍，望你能有所收获。


网页标题：SQLServer2005新功能之PIVOT的描述
标题链接：http://www.wenjiangwz.cn/article/cdodisg.html

其他资讯


关于我们
我们的服务
网站建设案例
新闻动态
联系我们

135-1821-9792

公司服务热线
友情链接： 成都汽车配件公司   成都写真广告   玻璃钢雕塑   成都包装设计公司   成都康明斯保养   成都大型发电机组出租   服务器托管机房   木包装箱   太阳能庭院灯   成都VI设计   

Copyright © 2009-2022 www.wenjiangwz.cn 成都网站建设公司 版权所有　蜀ICP备19037934号