有这样的表站名 年份 数据
AA 1986 23.5
AA 1987 25.6
AA 1988 23.0
BB 1986 21.0
BB 1987 22.0
BB 1988 22.7
CC 1986 21.8
CC 1987 23.0
CC 1988 21.8
......我想写成这样的表:
序号 年份 AA BB CC ....平均
1 1986 23.5 21.0 21.8 ....22.1
2 1987 25.6 22.0 23.0 ....23.5
3 1988 23.0 22.7 21.8 ....22.5
AA 1986 23.5
AA 1987 25.6
AA 1988 23.0
BB 1986 21.0
BB 1987 22.0
BB 1988 22.7
CC 1986 21.8
CC 1987 23.0
CC 1988 21.8
......我想写成这样的表:
序号 年份 AA BB CC ....平均
1 1986 23.5 21.0 21.8 ....22.1
2 1987 25.6 22.0 23.0 ....23.5
3 1988 23.0 22.7 21.8 ....22.5
set @sql='select 年份'
select @sql=@sql+',['+站名+']=max(case 站名 when '''+站名+''' then 数据 end)' from 表 group by 站名 order by 站名
set @sql=@sql+',avg(数据) as 平均 from 表 group by 年份'
exec(@sql)
set @sql = 'select 年份 '
select @sql = @sql + ' , sum(case 站名 when '''+ 站名 +''' then 数据 end) ['+ 站名 +']'
from (select distinct 站名 from #tab) as a
set @sql = @sql + ', avg(数据) as 平均 from #tab group by 年份'
exec(@sql)
insert #tab values('AA',1986,23.5)
insert #tab values('AA',1987,25.6)
insert #tab values('AA',1988,23.0)
insert #tab values('BB',1986,21.0)
insert #tab values('BB',1987,22.0)
insert #tab values('BB',1988,22.7)
insert #tab values('CC',1986,21.8)
insert #tab values('CC',1987,23.0)
insert #tab values('CC',1988,21.8)declare @sql varchar(4000)
set @sql = 'select 年份 '
select @sql = @sql + ' , sum(case 站名 when '''+ 站名 +''' then 数据 end) ['+ 站名 +']'
from (select distinct 站名 from #tab) as a
set @sql = @sql + ', avg(数据) as 平均 from #tab group by 年份'
exec(@sql) drop table #tab年份 AA BB CC 平均
---- ----- ----- ----- ----------
1986 23.5 21.0 21.8 22.100000
1987 25.6 22.0 23.0 23.533333
1988 23.0 22.7 21.8 22.500000
/*引用*/
1.包含两个表------典型行列转换问题例子
--建立测试环境
create table tb1 (id nvarchar(10),type nvarchar(10))
insert into tb1 select '11','a' union all select '22','b' union all select '33','c'
create table tb2 (n int,type nvarchar(10),num int)
insert into tb2 select '1','11','4' union all select '1','11','5'
union all select '2','22','8' union all select '3','22','5'
--查询处理
DECLARE @SQL VARCHAR(8000)
SET @SQL='select n '
SELECT @SQL= @SQL+',sum(case when type='+ttt+' then num else 0 end)['+tt+']' from
(select distinct a.type as tt,isnull(b.type,'0') as ttt from tb2 b right join tb1 a on a.id=b.type) b
set @sql=@sql+' from tb2 group by n'
print @sql
exec(@sql)
go--删除测试环境
Drop Table tb1,tb2
set @sql='select 年份'
select @sql=@sql+',['+站名+']=max(case 站名 when '''+站名+''' then 数据 end)' from 表 group by 站名 order by 站名
set @sql=@sql+',avg(数据) as 平均 from 表 group by 年份'
exec(@sql)
drop table test
go
create table test(站名 varchar(20) not null,年份 int not null,數據 decimal(15,1))
go
insert test
select 'AA',1986,23.5 union all
select 'AA',1987,25.6 union all
select 'AA',1988,23.0 union all
select 'BB',1986,21.0 union all
select 'BB',1987,22.0 union all
select 'BB',1988,22.7 union all
select 'CC',1986,21.8 union all
select 'CC',1987,23.0 union all
select 'CC',1988,21.8goselect * from test
/*
站名 年份 數據
AA 1986 23.5
AA 1987 25.6
AA 1988 23.0
BB 1986 21.0
BB 1987 22.0
BB 1988 22.7
CC 1986 21.8
CC 1987 23.0
CC 1988 21.8
*/declare @name varchar(1000)
set @name=''
select @name=@name+',max(case when 站名='''+站名+''' then 數據 end) ['+站名+']' from test group by 站名 order by 站名
select @name='select identity(int,1,1)序號,年份'+@name+',cast(avg(數據) as decimal(15,1)) 平均 into #a from test group by 年份 order by 1 select * from #a'
exec (@name)
/*
序號 年份 AA BB CC 平均
1 1986 23.5 21.0 21.8 22.1
2 1987 25.6 22.0 23.0 23.5
3 1988 23.0 22.7 21.8 22.5
*/
if exists(select name from sysobjects
where name = 'test' and type='u')
drop table test
gocreate table test(
station varchar(20) not null,
years int not null,
data decimal(15,1))
goinsert test
select 'AA',1986,23.5 union all
select 'AA',1987,25.6 union all
select 'AA',1988,23.0 union all
select 'BB',1986,21.0 union all
select 'BB',1987,22.0 union all
select 'BB',1988,22.7 union all
select 'CC',1986,21.8 union all
select 'CC',1987,23.0 union all
select 'CC',1988,21.8
goselect * from test------------执行需求 AfterTest 为结果表
if exists ( select name from sysobjects
where name='AfterTest'
and type = 'u'
)
drop table AfterTest
gocreate table dbo.AfterTest(
年份 int not null,
AA decimal(15,1),
BB decimal(15,1),
CC decimal(15,1),
平均 decimal(15,6)
)
godeclare @sql varchar(8000)
set @sql='select years'
select @sql=@sql+',['+station+']=max(case station when '''+station+''' then data end)'
from test
group by station
order by station
set @sql=@sql+',avg(data) as 平均 from test group by years'insert into AfterTest
exec(@sql)
goselect * from AfterTest
set @sql='select 年份'
select @sql=@sql+',['+站名+']=max(case 站名 when '''+站名+''' then 数据 end)' from 表 group by 站名 order by 站名
set @sql=@sql+',avg(数据) as 平均 from 表 group by 年份'
exec(@sql)