行列转换在编程中用的比较多,故稍微总结了下，具体看下面：
一.创建测试数据表
/*创建册数数据表【订单表】*/USE [TEST]GO/****** Object: Table [dbo].[订单表] Script Date: 12/03/2012 16:56:28 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[订单表]( [年度] [nchar](10) NULL, [月份] [int] NULL, [数量] [numeric](12, 0) NULL) ON [PRIMARY]GO------------END--------------------------------所有数据select * from 订单表
数据如下图：
二.实现方法如下：
--**************静态SQL******************************--1.行转列--【1】CASE……WHEN……THEN……ENDSELECT 年度,SUM(CASE(月份) WHEN 1 THEN 数量 END) "1月份",SUM(CASE(月份) WHEN 2 THEN 数量 END) "2月份",SUM(CASE(月份) WHEN 3 THEN 数量 END) "3月份",SUM(CASE(月份) WHEN 4 THEN 数量 END) "4月份",SUM(CASE(月份) WHEN 5 THEN 数量 END) "5月份",SUM(CASE(月份) WHEN 6 THEN 数量 END) "6月份",SUM(CASE(月份) WHEN 7 THEN 数量 END) "7月份",SUM(CASE(月份) WHEN 8 THEN 数量 END) "8月份",SUM(CASE(月份) WHEN 9 THEN 数量 END) "9月份",SUM(CASE(月份) WHEN 10 THEN 数量 END) "10月份",SUM(CASE(月份) WHEN 11 THEN 数量 END) "11月份",SUM(CASE(月份) WHEN 12 THEN 数量 END) "12月份"FROM 订单表GROUP BY 年度ORDER BY 年度
结果如下：
以下几种SQL写法稍微不同，结果都一样。（有静态有动态）
--【2】使用pivot转换——SQL 2005以上的版本才有的select * from 订单表 pivot (SUM(数量) for 月份 in("1","2","3","4","5","6","7","8","9","10","11","12"))aorder by 年度--【3】使用动态SQL--变量按sql语言顺序赋值declare @sql varchar(8000)set @sql=' SELECT 年度'select @[email protected]+',SUM(CASE CONVERT(VARCHAR(2),月份) WHEN '''+CONVERT(VARCHAR(2),月份)+''' THEN 数量 ELSE 0 END) ['+CONVERT(VARCHAR(2),月份)+']' from(SELECT DISTINCT 月份 FROM 订单表) as a --同 from 订单表 group by 月份set @[email protected]+' FROM 订单表 GROUP BY 年度 ORDER BY 年度'exec(@sql)--使用isnull(),变量先确定动态部分declare @sql varchar(8000)select @sql=ISNULL(@sql+',','')+' SUM(CASE CAST(月份 AS VARCHAR(2)) WHEN '''+CAST(月份 AS VARCHAR(2))+''' THEN 数量 ELSE 0 END) ['+CAST(月份 AS VARCHAR(2))+']'from(SELECT DISTINCT 月份 FROM 订单表)as a set @sql='SELECT 年度,[email protected]+' FROM 订单表 GROUP BY 年度 ORDER BY 年度'exec(@sql)--【4】使用stuff()——删除指定长度的字符，并在指定的起点处插入另一组字符。declare @sql varchar(8000)set @sql='' [email protected]select @[email protected]+',"'+CAST(月份 AS VARCHAR(2))+'"' from 订单表 group by 月份 --变量多值赋值set @sql=stuff(@sql,1,1,'') --去掉首个','set @sql='select * from 订单表 pivot (SUM(数量) for 月份 in ([email protected]+'))a'exec(@sql)--或使用isnull()declare @sql varchar(8000)select @sql=isnull(@sql+',"','"')+CAST(月份 AS VARCHAR(2))+'"' from 订单表 group by 月份set @sql='select * from 订单表 pivot (SUM(数量) for 月份 in ([email protected]+'))a'exec(@sql)
PIVOT的一般语法是：PIVOT(聚合函数(列) FOR列 in (…) )AS P
PIVOT用于将列值旋转为列名（即行转列），在SQL Server 2000可以用聚合函数配合CASE语句实现
注意：PIVOT、UNPIVOT是SQL Server 2005 的语法，使用需修改数据库兼容级别
【待续……】