在论坛中出现的比较难的sql问题：37(动态行转列 某一行数据转为列名)

最近，在论坛中，遇到了不少比较难的sql问题，虽然自己都能解决，但发现过几天后，就记不起来了，也忘记解决的方法了。

所以，觉得有必要记录下来，这样以后再次碰到这类问题，也能从中获取解答的思路。

sql语句，某一行数据变为列名。

http://bbs.csdn.net/topics/390622184?page=1#post-395860916
我有一个表
   日期       合肥   F3      F4       芜湖   F6      F7
--------------------------------------------------------------------
   NULL       气象   最高    最低     气象   最高    最低
   2013-10    多云   32      20       雨     35      20
   2013-11    晴     30      20       晴     25      20

现在要变为：
   日期           城市    气象    最高     最低
--------------------------------------------------------------
   2013-10        合肥    多云    32        20
   2013-10        芜湖    雨      35        20
   2013-11        合肥    晴      30        20
   2013-11        芜湖    晴      25        20
可以的话尽量写成动态，因为不只两个城市
如果我的第一个表是这样呢：
   日期       合肥   F3      F4       芜湖   F6      F7     北京   F9    F10    福州   F12  F13
------------------------------------------------------------------------------------------------
   NULL       气象   最高    最低     气象   最高    最低   气象   最高   最低  气象   最高  最低
   2013-10    多云   32      20       雨     35      20      晴     32     20   多云   25    20
   2013-11    晴     30      20       晴     25      20      雨     31     21   晴     25    21
你能否写一个循环语句，首先城市的数量是（最后一个列名的阿拉伯数字-1）/3,然后再作判断写出像你第一个贴得出的结果集。

我的解法：

if object_id('tb') is not null
   drop table tb
go

create table tb
(
日期 varchar(15),
合肥 varchar(10),
F3 varchar(10),
F4 varchar(10),
芜湖 varchar(10),
F6 varchar(10),
F7 varchar(10),
北京 varchar(10),
F9 varchar(10),
F10 varchar(10),
福州 varchar(10),
F12 varchar(10),
F13 varchar(10)
)

insert into tb
select NULL,'气象', '最高','最低','气象','最高','最低','气象','最高','最低' ,'气象','最高','最低' union all
select '2013-10','多云', '32' ,'20', '雨','35', '20', '晴','32','20' , '多云','25','20' union all
select '2013-11','晴',  '30','20','晴','25', '20', '雨','31','21','晴','25' , '21' 
go


declare @tb_name nvarchar(100);
declare @t table(tb_name nvarchar(100),column_name nvarchar(100),column_id int) 

declare @i int
declare @count int
declare @sql nvarchar(max);

--这里的表为 tb，需要换成你自己的表名
set @tb_name = 'tb'

insert into @t
select t.name ,
       c.name,
       c.column_id
from sys.tables t
inner join sys.columns c
        on t.object_id = c.object_id
where t.name = @tb_name  


set @i = 2
set @count = (select count(*) from @t)
set @sql ='';

while @i < @count
begin
   set @sql = @sql + 'union all select 日期,''' + 
              (select column_name from @t where column_id = @i) + ''' as 城市,' +
              (select column_name from @t where column_id = @i) + ' as 气象,' + 
              (select column_name from @t where column_id = @i+1) + ' as 最高,'+
              (select column_name from @t where column_id = @i+1+1) + ' as 最低 ' +
              ' from ' + (select distinct tb_name from @t) + ' where 日期 is not null '
              
   set @i = @i + 3
end

select @sql = stuff(@sql,1,len('union all'),'')

--select @sql

exec(@sql)
/*
日期              城市   气象         最高         最低
--------------- ---- ---------- ---------- ----------
2013-10         合肥   多云         32         20
2013-11         合肥   晴          30         20
2013-10         芜湖   雨          35         20
2013-11         芜湖   晴          25         20
2013-10         北京   晴          32         20
2013-11         北京   雨          31         21
2013-10         福州   多云         25         20
2013-11         福州   晴          25         21
*/

动态生成的sql语句：

 select 日期,'合肥' as 城市,合肥 as 气象,F3 as 最高,F4 as 最低  
 from tb where 日期 is not null 
 union all 
 select 日期,'芜湖' as 城市,芜湖 as 气象,F6 as 最高,F7 as 最低  
 from tb where 日期 is not null 
 union all 
 select 日期,'北京' as 城市,北京 as 气象,F9 as 最高,F10 as 最低  
 from tb where 日期 is not null 
 union all 
 select 日期,'福州' as 城市,福州 as 气象,F12 as 最高,F13 as 最低  
 from tb where 日期 is not null