行列转换之列不固定

之前写过一篇文章是专门说明对于固定列如何进行行列转换，也就是说最终期望的结果集的列是固定，例如期望的结果是各个城市一年之中每个月的经济量，这个很好做，按照我之前写的文章操作即可。现在有种情况就是期望的结果列不固定，例如求某个月各个城市每天的经济量，由于月有的是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文档格式的字符串

最后附上另外一篇讲解如何对于固定列进行行列转换的文章

http://blog.youkuaiyun.com/dotnetstudio/article/details/9790671