select 學號,'','語文','數學','英語','物理','化學' from a group by 學號
union
select 學號,月份,語文,數學,英語,物理,化學 from A
order by 學號,case 科目1 when '語文' then '1' else 2 end
union
select 學號,月份,語文,數學,英語,物理,化學 from A
order by 學號,case 科目1 when '語文' then '1' else 2 end
@s = 'select distinct '+name+',null' ,
@v = 'select '+name
from
syscolumns
where
id=object_id('表名') and colid=1
select
@v = @v + ','+name
from
syscolumns
where
id=object_id('表名') and colid=2
select
@s = @s + ','''+name+'''',
@v = @v + ',cast('+name+' as varchar(100))'
from
syscolumns
where
id=object_id('表名') and colid>2set @s = @s + ' from 表名 union all '+ @v + ' from 表名'print @sexec(@s)
(
c1 varchar(10),
c2 varchar(10),
c3 int
)
insert @tb
select 'a','2005-05-01',21 union
select 'a','2005-05-31',15 union
select 'c','2005-06-01',18 union
select 'c','2005-06-30',15 union
select 'f','2005-07-01',19 union
select 'f','2005-07-31',26 union
select 'g','2005-08-31',19
create table A
(
[學號] varchar(10),
[月份] varchar(10),
[語文] varchar(10),
[數學] varchar(10),
[英語] varchar(10),
[物理] varchar(10),
[化學] varchar(10)
)
insert A
select '001','7月',80,90,85,70,84 union
select '001','8月',79,89,91,84,70
go--查询
declare @sql varchar(8000),@i int
select @i=isnull(@i,0)+1,
@sql=isnull(@sql,'')+','''+name+''' as [科目'+convert(varchar,@i)+']'
from syscolumns
where id=object_id('A') and name not in ('學號','月份')
order by colorder
set @sql='select *'
+' from'
+' ('
+' select [學號],'''' as [月份] '+@sql+' from A group by [學號]'
+' union all '
+' select * from A'
+' )t'
+' order by [學號]'
exec(@sql)--删除测试环境
drop table A--结果
/*
學號 月份 科目1 科目2 科目3 科目4 科目5
---------- ---------- ---------- ---------- ---------- ---------- ----------
001 語文 數學 英語 物理 化學
001 7月 80 90 85 70 84
001 8月 79 89 91 84 70
*/