表中数据
yzid xm je
0100215 气费 10.000
0100215 清洁费 20.000
0100215 物管费 120.000
yzid0000002 气费 10.000
yzid0000002 清洁费 200.000
yzid0000002 物管费 1200.000
yzid000001 气费 1.000
yzid000001 清洁费 10.000
yzid000001 物管费 0.000想得到的结果是
yzid 气费 清洁费 物管费
0100215 10 20 120
yzid0000002 10 200 1200
yzid000001 1 10 0帮下忙呀!!!
yzid xm je
0100215 气费 10.000
0100215 清洁费 20.000
0100215 物管费 120.000
yzid0000002 气费 10.000
yzid0000002 清洁费 200.000
yzid0000002 物管费 1200.000
yzid000001 气费 1.000
yzid000001 清洁费 10.000
yzid000001 物管费 0.000想得到的结果是
yzid 气费 清洁费 物管费
0100215 10 20 120
yzid0000002 10 200 1200
yzid000001 1 10 0帮下忙呀!!!
pivot(
max(je) for xm in (气费,清洁费,物管费)
)
p
-------------------------------------------> 测试时间:2009-07-19
--> 我的淘宝:http://shop36766744.taobao.com/--------------------------------------------------if object_id('[TB]') is not null drop table [TB]
create table [TB]([yzid] varchar(11),[xm] varchar(6),[je] numeric(7,3))
insert [TB]
select '0100215','气费',10.000 union all
select '0100215','清洁费',20.000 union all
select '0100215','物管费',120.000 union all
select 'yzid0000002','气费',10.000 union all
select 'yzid0000002','清洁费',200.000 union all
select 'yzid0000002','物管费',1200.000 union all
select 'yzid000001','气费',1.000 union all
select 'yzid000001','清洁费',10.000 union all
select 'yzid000001','物管费',0.000declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([xm])+'=max(case when [xm]='+quotename([xm],'''')+' then [je] else 0 end)'
from TB group by[xm]
exec('select [yzid]'+@s+' from TB group by [yzid]')/*(所影响的行数为 9 行)yzid 气费 清洁费 物管费
----------- --------- --------- ---------
0100215 10.000 20.000 120.000
yzid0000002 10.000 200.000 1200.000
yzid000001 1.000 10.000 .000
*/drop table TB
select yzid ,
气费=MAX(case when XM='气费' then je else 0 end),
清洁费=MAX(case when XM='清洁费' then je else 0 end),
物管费=MAX(case when XM='物管费' then je else 0 end)
from tb
group by yzid
drop table [TB]
go
create table [TB]([yzid] varchar(11),[xm] varchar(6),[je] numeric(7,3))
insert [TB]
select '0100215','气费',10.000 union all
select '0100215','清洁费',20.000 union all
select '0100215','物管费',120.000 union all
select 'yzid0000002','气费',10.000 union all
select 'yzid0000002','清洁费',200.000 union all
select 'yzid0000002','物管费',1200.000 union all
select 'yzid000001','气费',1.000 union all
select 'yzid000001','清洁费',10.000 union all
select 'yzid000001','物管费',0.000--2000的方法
select yzid ,
气费=MAX(case when XM='气费' then je else 0 end),
清洁费=MAX(case when XM='清洁费' then je else 0 end),
物管费=MAX(case when XM='物管费' then je else 0 end)
from tb
group by yzid
--2005的方法
select * from tb
pivot(
max(je) for xm in (气费,清洁费,物管费)
)
p
/*
yzid 气费 清洁费 物管费
----------- --------------------------------------- --------------------------------------- ---------------------------------------
0100215 10.000 20.000 120.000
yzid0000002 10.000 200.000 1200.000
yzid000001 1.000 10.000 0.000(3 行受影响)
*/
然后组合成一个化行为列的sql,会使用到@fieldnamedeclare @sql varchar(8000),@fn varchar(8000)
set @fn=','
select @fn=@fn+'f0'+xm+'f1,' from (
select distinct xm
from tb
) aset @sql='...