SqlServcer行转列和列转行说明.docxVIP

SQL Server 行转列、列转行概述在做管材到站统计分析时，有很多地方需要将行数据转换为列信息。使用原有的行转列方法一个一个数据处理比较繁琐 ，使用Piovt方法进行数据行转列处理，就很方便快捷的实现预期的结果。使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT 与 PIVOT 执行相反的操作，将表值表达式的列转换为列值。Pivot定义 SELECT 非转换列, 行转换为列名称FROM源表PIVOT ( 聚合函数 (列) FOR 需要转换为行的列 IN ( [转换后的列] … ) //[转换后的列名称] ) AS 数据透视表 可选的ORDER BY子句; 注：PIVOT子句内的 [转换后的列] 列的值都是需要转换为列的常量值,需要用[]括起，支持GUID，字符串及各种数字。PIVOT 提供的语法比一系列复杂的 SELECT...CASE 语句中所指定的语法更简单和更具可读性。实例 （1）转换为列的行数据比较少或是固定的/* 创建表，插入数据*/create table Mon ( YearM nvarchar(100), Value numeric(18,2))insert Mon select 2012年1月,567union allselect 2012年2月,345union allselect 2012年3月,123union allselect 2012年4月,3322union allselect 2012年5月,2334union allselect 2012年6月,565union allselect 2012年7月,77查询后结果如下：select * from dbo.Mon我们需要一行中按月份由1月到7月显示Value，需要这样的结果使用Pivot方法在sql中实现上述效果： select [2012年1月],[2012年2月],[2012年3月],[2012年4月],[2012年5月],[2012年6月],[2012年7月] from Mon pivot( sum(Value) for YearM in([2012年1月],[2012年2月],[2012年3月],[2012年4月],[2012年5月],[2012年6月],[2012年7月]))as pvt注：要转换为列的行数据比较少或是固定的时候，可以直接列出显示的列信息。上述源数据表中YearM的值是唯一的（2）动态的行转列 要转换的行数据比较多或者不是固定的信息，可以使用字符串拼接转换后的列。--动态获取年份declare @Yea nvarchar(100)--拼接字符串获取转换后列信息select @Yea=ISNULL(@Yea+,,)+ [+YearM+] from Mon Exec(select +@Yea+ from Mon pivot( sum(Value) for YearM in(+@Yea+))as pvt)(3) 需要转换多列数据/*创建表，插入数据*/create table MonPlan ( YearMP nvarchar(100), YearMR nvarchar(100), Value numeric(18,2), PlanWk numeric(18,2))insert MonPlan select 2012年1月计划,2012年1月实际,567,243union allselect 2012年2月计划,2012年2月实际,345,256union allselect 2012年3月计划,2012年3月实际,123,883union allselect 2012年4月计划,2012年4月实际,3322,243union allselect 2012年5月计划,2012年5月实际,2334,990union allselect 2012年6月计划,2012年6月实际,565,456union allselect 2012年7月计划,2012年7月实际,77,789查询结果select * from dbo.MonPlan要将计划和实际作为列信息value和PlanWk分别作为其值：使用Pivot进行行转列的sql语句为：--动态获取年份declare @Yea nvarchar(100)declare @Real nvarchar(100)declare @Dt nvarchar(1000)--拼接字符串获取转换后列信息select @Yea=ISNULL(@Yea+,,)+ [+YearMP+] ,@Real=ISNULL(@Rea