日期:2014-05-18  浏览次数:20478 次

求个列转行写法!
SQL code

---原资料:
areaName iQuantity       QMoney         perc
---------------------------------------------------
NULL    1012237.00    215459.90     5.02%
厂部    2324811.00    662078.39     15.43%
华北办    218338.50             111101.00             2.59%
华东办    1843281.00    893927.53             20.83%
华南办    4664796.60    787601.76            18.35%
华中    7223839.60    1621436.25    37.78%
--------------------------------------------------


SQL code

需求结果为:
-------------------------------------------------------------
           NULL              厂部          华北办        华东办         华南办        华中    
-------------------------------------------------------------
iQuantity  1012237.00       2324811.00    218338.50     1843281.00     4664796.60   7223839.60
QMoney     215459.90        662078.39     111101.00     893927.53      787601.76    1621436.25
perc       5.02%            15.43%        2.59%         20.83%         18.35%       37.78%


注意:环境为SQL2000   areaName列为不确定,即需动态





------解决方案--------------------
SQL code

create table tb(areaName varchar(10),iQuantity numeric(10,2),
QMoney numeric(10,2),perc varchar(10))

insert into tb
select null,1012237.00,215459.90,'5.02%' union all
select '厂部',2324811.00,662078.39,'15.43%' union all
select '华北办',218338.50,111101.00,'2.59%'

select ISNULL(areaName,'null') areaName,rtrim(iQuantity) as col1,'iQuantity' col2 
into #tb from tb
union all
select ISNULL(areaName,'null'),rtrim(QMoney) as col1,'QMoney' col2 from tb
union all
select ISNULL(areaName,'null'),perc as col1,'perc' from tb

declare @sql varchar(8000)
set @sql='select col2'
select @sql=@sql+',max(case areaName when '''+areaName+''' then col1 end) 
                 as ['+areaName+']'
from #tb group by areaName
set @sql=@sql+' from #tb group by col2'
exec(@sql)
drop table #tb

/*
col2      null        厂部                                        华北办
--------- ----------- ----------------------------------------- ----------
iQuantity 1012237.00  2324811.00                                218338.50
perc      5.02%       15.43%                                    2.59%
QMoney    215459.90   662078.39                                 111101.00