create table testTable
(
YearMonth varchar(6),--年月
Income decimal(18,2),--收入
Expenditure decimal(18,2)--支出)
insert into testtable
select '200801',1800,1500
union all
select '200802',2100,2500
union all
select '200803',3210,2100select * from testtable我想这样显示数据
年月200801 200802 200803
收入1800 2100 3210
支出1500 2500 2100
谢谢大家的回复
(
YearMonth varchar(6),--年月
Income decimal(18,2),--收入
Expenditure decimal(18,2)--支出)
insert into testtable
select '200801',1800,1500
union all
select '200802',2100,2500
union all
select '200803',3210,2100select * from testtable我想这样显示数据
年月200801 200802 200803
收入1800 2100 3210
支出1500 2500 2100
谢谢大家的回复
(
YearMonth varchar(6),--年月
Income decimal(18,2),--收入
Expenditure decimal(18,2)--支出)
insert into testtable
select '200801',1800,1500
union all
select '200802',2100,2500
union all
select '200803',3210,2100
--> 静态
select
Item,
[200801]=sum(case YearMonth when '200801' then Value else 0 end),
[200802]=sum(case YearMonth when '200802' then Value else 0 end),
[200803]=sum(case YearMonth when '200803' then Value else 0 end)
from
(
select YearMonth,Item='收入',Value=Income from testtable
union all
select YearMonth,'支出',Value=Expenditure from testtable
) as t
group by Item--> 动态
declare @cols varchar(8000)
select @cols=isnull(@cols+',','')+'['+YearMonth+']=sum(case YearMonth when '''+YearMonth+''' then Value else 0 end)' from testtable
exec
('
select Item,'+@cols+' from
(
select YearMonth,Item=''收入'',Value=Income from testtable
union all
select YearMonth,''支出'',Value=Expenditure from testtable
) as t
group by Item
')/*
Item 200801 200802 200803
----------- ----------- ----------- -----------
收入 1800.00 2100.00 3210.00
支出 1500.00 2500.00 2100.00
*/