行列转换之列不固定

来源：互联网发布：网络文明宣传语编辑：程序博客网时间：2024/05/29 04:23

之前写过一篇文章是专门说明对于固定列如何进行行列转换，也就是说最终期望的结果集的列是固定，例如期望的结果是各个城市一年之中每个月的经济量，这个很好做，按照我之前写的文章操作即可。现在有种情况就是期望的结果列不固定，例如求某个月各个城市每天的经济量，由于月有的是30天有的是31天，这样就导致无法确定列，用之前的方法显然搞不定。那么这个时候我们就需要采用动态SQL去实时的拼接列名。

创建表语句

USE [master]GO/****** Object:  Table [dbo].[Table_4]    Script Date: 08/09/2013 16:05:28 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[Table_4]([time] [datetime] NULL,[City] [varchar](10) NULL,[count] [int] NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFGO

插入测试数据

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.csdn.net/dotnetstudio/article/details/9790671