SqlServcer行转列及列转行说明.docx

SQL Server 行转列、列转行1、 概述在做管材到站统计分析时，有很多地方需要将行数据转换为列信息。使用原有的行转列方法一个一个数据处理比较繁琐 ，使用Piovt方法进行数据行转列处理，就很方便快捷的实现预期的结果。使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT 与 PIVOT 执行相反的操作，将表值表达式的列转换为列值。2、 Pivot定义 SELECT , FROMPIVOT ( () FOR IN ( 转换后的列 ) /转换后的列名称 ) AS ; 注：PIVOT子句内的 转换后的列 列的值都是需要转换为列的常量值,需要用括起，支持GUID，字符串及各种数字。PIVOT 提供的语法比一系列复杂的 SELECT.CASE 语句中所指定的语法更简单和更具可读性。3、 实例 （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(Real+,)+ +YearMR+,Dt= ISNULL(Dt+,)+sum(isnull(+YearMP+,0) as +YearMP+,+sum(isnull(+YearMR+,0) as +YearMR+from MonPlan print yeaprint realprint dtExec(select +Dt+ from MonPlan pivot( sum(Value) for YearMP in(+Yea+)as pvtpivot( sum(PlanWk) for YearMR in (+Real+) as pt)4、列转行select 2012年1月,2012年2月,2012年3月,2012年4月,2012年5月,2012年6月,2012年7月 into #Tfrom Mon pivot( sum(Value) for YearM in(2012年1月,2012年2月,2012年3月,2012年4月,2012年5月,2012年6月,2012年7月)as pvt查询结果 UNPIVOT 将与 PIVOT 执行几乎完全相反的操作，将列转换为行。以上示例中生成的表在数据库中存储为 #T，需要将列标识符 2012年1月,2012年2月,2012年3月,2012年4月,2012年5月,2012年6月,2012年7月 旋转为对应于特定的行值。这意味着必须标识另外两个列。包含要旋转的列值（2012年1月,2012年2月.）的列将被称为 YearM，将保存当前位于待旋转列下的值的列被称为 value。Exec(select YearM,value from #Tunpivot( value for YearM in (2012年1月,2012年2月,2012年3月,2012年4月,2012年5月,2012年6月,2012年7月 ) as pt)drop table #T注：UNPIVOT 并不完全是 PIVOT 的逆操作。PIVOT 会执行一次聚合，从而将多个可能的行合并为输出中的单个行。而 UNPIVOT 不会重现原始表值表达式的结果，因为行已经被合并了。 update Mon set value=null where Yearm =2012年1月select 2012年月,2012年月,2012年月,2012年月,2012年月,2012年月,2012年月 into #Tfrom Mon pivot( sum(Value) for YearM in(2012年月,2012年月,2012年月,2012年月,2012年月,2012年月,2012年月)as pvtExec(select YearM,value