之前写过一篇文章是专门说明对于固定列如何进行行列转换，也就是说最终期望的结果集的列是固定，例如期望的结果是各个城市一年之中每个月的经济量，这个很好做，按照我之前写的文章操作即可。现在有种情况就是期望的结果列不固定，例如求某个月各个城市每天的经济量，由于月有的是30天有的是31天，这样就导致无法确定列，用之前的方法显然搞不定。那么这个时候我们就需要采用动态SQL去实时的拼接列名。
创建表语句
USE [master] GO /****** Object: Table [dbo].[Table_4] Script Date: 08/09/2013 16:05:28 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Table_4]( [time] [datetime] NULL, [City] [varchar](10) NULL, [count] [int] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
插入测试数据
insert into [master].[dbo].[Table_4] ([time],[City],[count]) values( '2013-07-01 00:00:00.000' , '北京市' , 14 ); insert into [master].[dbo].[Table_4] ([time],[City],[count]) values( '2013-07-01 00:00:00.000' , '天津市' , 92 ); insert into [master].[dbo].[Table_4] ([time],[City],[count]) values( '2013-07-01 00:00:00.000' , '上海市' , 8 ); insert into [master].[dbo].[Table_4] ([time],[City],[count]) values( '2013-07-01 00:00:00.000' , '大连市' , 3 ); insert into [master].[dbo].[Table_4] ([time],[City],[count]) values( '2013-07-01 00:00:00.000' , '无锡市' , 142 ); insert into [master].[dbo].[Table_4] ([time],[City],[count]) values( '2013-07-01 00:00:00.000' , '深圳市' , 4 ); insert into [master].[dbo].[Table_4] ([time],[City],[count]) values( '2013-07-01 00:00:00.000' , '南京市' , 1 ); insert into [master].[dbo].[Table_4] ([time],[City],[count]) values( '2013-07-02 00:00:00.000' , '北京市' , 11 ); insert into [master].[dbo].[Table_4] ([time],[City],[count]) values( '2013-07-02 00:00:00.000' , '天津市' , 79 ); insert into [master].[dbo].[Table_4] ([time],[City],[count]) values( '2013-07-02 00:00:00.000' , '上海市' , 3 ); insert into [master].[dbo].[Table_4] ([time],[City],[count]) values( '2013-07-02 00:00:00.000' , '无锡市' , 92 ); insert into [master].[dbo].[Table_4] ([time],[City],[count]) values( '2013-07-02 00:00:00.000' , '深圳市' , 4 ); insert into [master].[dbo].[Table_4] ([time],[City],[count]) values( '2013-07-03 00:00:00.000' , '北京市' , 10 ); insert into [master].[dbo].[Table_4] ([time],[City],[count]) values( '2013-07-03 00:00:00.000' , '天津市' , 80 );
原始表查询结果
期望的结果是按城市统计每个城市每天的经济量
这个地方时间是不固定的，因为每个月的天数不一样，因此下面采用动态拼接列的方式实现
Declare @sql varchar(max)
set @sql=STUFF((select ','+QUOTENAME(CONVERT(varchar(10),[time],120)) from [Table_4] group by [time] FOR XML PATH('')) ,1,1,'')
set @sql='select * from [Table_4] t
pivot (sum([count])for [time] in ('+@sql+')) a '
exec(@sql)
这里有必要讲解几个关键字
SELECT stuff('NBA',1,1,'C')
这个结果就是CBA，该函数主要起到替换字符的作用，第一个参数是待被替换的字符串，第二个参数是替换的起点，第三个参数是从起点开始多少个字符将被替换，第四个参数是要替换的字符串。
QUOTENAME关键字用来给名称加上[]，对于时间必须要加上这个，否则不知道其为列名
FOR XML PATH('')这个可以将查询到的结果集转换为XML文档格式的字符串
最后附上另外一篇讲解如何对于固定列进行行列转换的文章