create table #t([Year] int,[Month] int,DataValue int);
insert into #t select 2005,1,10
union all select 2005,2,20
union all select 2005,3,30
union all select 2005,4,40
union all select 2005,5,50
union all select 2005,6,60
union all select 2005,7,70
union all select 2005,12,120
union all select 2006,1,20
union all select 2006,2,40;
select * from #t;declare @sql varchar(8000);
set @sql='select year'
select @sql=rtrim(@sql)+',min(case month when '+rtrim(cast(month as char))+' then datavalue end) as ['+rtrim(cast(month as char))+'月]' from (select distinct month from #t) t;
set @sql=rtrim(@sql)+' from #t group by year';
print(@sql);
exec(@sql);
drop table #t;
怎么样把转成列的数据放到临时表中,怎么加都有错误的!!!晕
insert into #t select 2005,1,10
union all select 2005,2,20
union all select 2005,3,30
union all select 2005,4,40
union all select 2005,5,50
union all select 2005,6,60
union all select 2005,7,70
union all select 2005,12,120
union all select 2006,1,20
union all select 2006,2,40;
select * from #t;declare @sql varchar(8000);
set @sql='select year'
select @sql=rtrim(@sql)+',min(case month when '+rtrim(cast(month as char))+' then datavalue end) as ['+rtrim(cast(month as char))+'月]' from (select distinct month from #t) t;
set @sql=rtrim(@sql)+' from #t group by year';
print(@sql);
exec(@sql);
drop table #t;
怎么样把转成列的数据放到临时表中,怎么加都有错误的!!!晕
解决方案 »
- 关于sp_executesql问题
- 怎么样用sql server 代码 select 某个出存储过程的所有代码
- 如何确定某条记录在表中排在第几位?
- 如何建立一个满足以下要求的SQL数据库?
- 急等着解决的一个问题,求兄弟们帮忙了!
- 如何实现不同数据库中两张表的完全同步?
- 请教一个sql语言的问题!
- SQL中如何修改表的列名呢???
- 百家争鸣:数据库设计的关键之处在哪里?或者说其精要之处?
- 请问SQLServer2000为什么没办法用SQLserver身分注册,只能用window身分注册呢?到底该如何办?
- 请中级DBA推荐几本书,要求关于数据库优化管理的
- 是不是sql2005寫的數據庫不能附加到sql2000中去?
2005 10 20 30 40 50 60 70 120
2006 20 40 NULL NULL NULL NULL NULL NULL*/
insert into #t select 2005,1,10
union all select 2005,2,20
union all select 2005,3,30
union all select 2005,4,40
union all select 2005,5,50
union all select 2005,6,60
union all select 2005,7,70
union all select 2005,12,120
union all select 2006,1,20
union all select 2006,2,40;
select * from #t;declare @sql varchar(8000);
set @sql='select year '
select @sql=rtrim(@sql)+',min(case month when '+rtrim(cast(month as char))+' then datavalue end) as ['+rtrim(cast(month as char))+'月]' from (select distinct month from #t) t;
set @sql=rtrim(@sql)+' into ##tt from #t group by year';exec(@sql)select * from ##ttyear 1月 2月 3月 4月 5月 6月 7月 12月
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2005 10 20 30 40 50 60 70 120
2006 20 40 NULL NULL NULL NULL NULL NULL(2 row(s) affected)