行列轉換之列不固定

之前寫過一篇文章是專門說明對於固定列如何進行行列轉換，也就是說最終期望的結果集的列是固定，例如期望的結果是各個城市一年之中每個月的經濟量，這個很好做，按照我之前寫的文章操作即可。現在有種情況就是期望的結果列不固定，例如求某個月各個城市每天的經濟量，由於月有的是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