create table T(DateClass char(7), Class varchar(10), [Count] varchar(10)) insert T select '2001','本科一批','0001' union all select '2001','本科二批','0002' union all select '2002','本科三批','0003' union all select '2002','本科二批','0004' union all select '2003','本科一批','0005' union all select '2003','本科二批','0007' union all select '2005','本科三批','0008'declare @sql varchar(2000) set @sql = 'select DateClass 编号'select @sql = @sql + ',max(case Class when '''+a.Class+''' then right(count,4) else 0 end ) as ['+a.Class+']' from (select distinct Class from t)aset @sql = @sql + ' from t group by DateClass 'exec(@sql)drop table t
--------------------------create table //用汉语拼音代替的汉字,不好意思 -_-!!create table t(province nvarchar(100) ,class nvarchar(20) ,[year] int ,grade nvarchar(20) ,line int ) insert into t select 'beijing', 'benke', 2001,'1pi',500 union all select 'tianjin', 'benke', 2001,'1pi',520 union all select 'beijing', 'benke', 2001,'2pi', 480 union all select 'beijing', 'benke', 2001, '3pi', 440 union all select 'tianjin', 'benke', 2001, '2pi', 505 union all select 'tianjin', 'benke', 2001, '3pi', 585 union select 'beijing', 'benke', 2002,'1pi',510 union all select 'tianjin', 'benke', 2002,'1pi',530 union all select 'beijing', 'benke', 2002,'2pi', 490 union all select 'beijing', 'benke', 2002, '3pi', 450 union all select 'tianjin', 'benke', 2002, '2pi', 515 union all select 'tianjin', 'benke', 2002, '3pi', 495 ------SQL declare @sql varchar(8000) select @sql = 'select province,[year]' select @sql = @sql + ',min(case grade when '''+ grade +''' then line end)[' + class +''+grade + ']' from (select distinct class,grade from t) as a select @sql = @sql + ' from t group by province,[year]'exec (@sql)----Result province year benke1pi benke2pi benke3pi beijing 2001 500 480 440 tianjin 2001 520 505 585 beijing 2002 510 490 450 tianjin 2002 530 515 495
create table T(DateClass char(7), Class varchar(10), [Count] varchar(10))
insert T
select '2001','本科一批','0001'
union all
select '2001','本科二批','0002'
union all
select '2002','本科三批','0003'
union all
select '2002','本科二批','0004'
union all
select '2003','本科一批','0005'
union all
select '2003','本科二批','0007'
union all
select '2005','本科三批','0008'declare @sql varchar(2000)
set @sql = 'select DateClass 编号'select @sql = @sql + ',max(case Class when '''+a.Class+''' then right(count,4) else 0 end ) as ['+a.Class+']'
from (select distinct Class from t)aset @sql = @sql + ' from t group by DateClass 'exec(@sql)drop table t
//用汉语拼音代替的汉字,不好意思 -_-!!create table t(province nvarchar(100)
,class nvarchar(20)
,[year] int
,grade nvarchar(20)
,line int
)
insert into t
select 'beijing', 'benke', 2001,'1pi',500
union all
select 'tianjin', 'benke', 2001,'1pi',520
union all
select 'beijing', 'benke', 2001,'2pi', 480
union all
select 'beijing', 'benke', 2001, '3pi', 440
union all
select 'tianjin', 'benke', 2001, '2pi', 505
union all
select 'tianjin', 'benke', 2001, '3pi', 585
union
select 'beijing', 'benke', 2002,'1pi',510
union all
select 'tianjin', 'benke', 2002,'1pi',530
union all
select 'beijing', 'benke', 2002,'2pi', 490
union all
select 'beijing', 'benke', 2002, '3pi', 450
union all
select 'tianjin', 'benke', 2002, '2pi', 515
union all
select 'tianjin', 'benke', 2002, '3pi', 495
------SQL
declare @sql varchar(8000)
select @sql = 'select province,[year]'
select @sql = @sql + ',min(case grade when '''+ grade
+''' then line end)[' + class +''+grade + ']'
from (select distinct class,grade from t) as a
select @sql = @sql + ' from t group by province,[year]'exec (@sql)----Result
province year benke1pi benke2pi benke3pi
beijing 2001 500 480 440
tianjin 2001 520 505 585
beijing 2002 510 490 450
tianjin 2002 530 515 495