use Tempdb
go
--> --> (Roy)生成測試數據
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([项目] nvarchar(1),[年] int,[月] int,[金额] int)
Insert #T
select N'a','2001',1,1000 union all
select N'a','2002',1,2000 union all
select N'a','2001',2,3000 union all
select N'a','2002',2,4000 union all
select N'b','2003',1,5000 union all
select N'b','2004',1,6000
Go
declare @s nvarchar(4000)
set @s='select [项目],[月]'
Select @s=@s+',['+rtrim([年])+']=max(case when [年]='+rtrim([年])+' then rtrim([金额]) else '''' end)'
from #T group by [年]
exec(@s+' from #T group by [项目],[月] order by 1')(6 行受影响)
项目 月 2001 2002 2003 2004
---- ----------- ------------ ------------ ------------ ------------
a 1 1000 2000
a 2 3000 4000
b 1 5000 6000(3 行受影响)
go
--> --> (Roy)生成測試數據
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([项目] nvarchar(1),[年] int,[月] int,[金额] int)
Insert #T
select N'a','2001',1,1000 union all
select N'a','2002',1,2000 union all
select N'a','2001',2,3000 union all
select N'a','2002',2,4000 union all
select N'b','2003',1,5000 union all
select N'b','2004',1,6000
Go
declare @s nvarchar(4000)
set @s='select [项目],[月]'
Select @s=@s+',['+rtrim([年])+']=max(case when [年]='+rtrim([年])+' then rtrim([金额]) else '''' end)'
from #T group by [年]
exec(@s+' from #T group by [项目],[月] order by 1')(6 行受影响)
项目 月 2001 2002 2003 2004
---- ----------- ------------ ------------ ------------ ------------
a 1 1000 2000
a 2 3000 4000
b 1 5000 6000(3 行受影响)
解决方案 »
- 我的SQL server 2005 软件每次运行都有使用“以管理员的身份运行”,
- 求一个SQL提示工具
- 【All】求一统计语句
- 帮我看看这句话是啥意思. insert statement cnflicted with colum foreign key constraint 'FK_Sale_Detail_Sale_Master'. The conflict
- MSSQL中有没有数据负载均衡的技术啊?
- 存储过程中使用循环的问题?
- 执行SQL在不同服务器上执行结果出错.跟踪结果详见图片
- management studio express中新建数据库出错
- 请问索引可不可以建立在字段的部分内容上
- 如何用存储过程导出某查询返回的结果集?
- 这样的功能用Sql语句如何实现
- 为什么有些表“新建索引”是灰色的?
create table tb061603(xm varchar(5),nn varchar(4),yy varchar(2),jj money)
insert into tb061603
select 'a','2001','1',1000 union all
select 'a','2002','1',2000 union all
select 'a','2001','2',3000 union all
select 'a','2002','2',4000 union all
select 'b','2003','1',5000 union all
select 'b','2004','1',6000 select * from
(select xm,yy,jj,nn from tb061603) a
pivot
(
sum(jj) for nn in ([2001],[2002],[2003],[2004])
) as b;
/*
xm yy 2001 2002 2003 2004
----- ---- --------------------- --------------------- --------------------- ---------------------
a 1 1000.00 2000.00 NULL NULL
b 1 NULL NULL 5000.00 6000.00
a 2 3000.00 4000.00 NULL NULL(3 行受影响)*/