原表中的数据是这样的:
课目 成绩
数学 105
数学 91
数学 81
数学 87
数学 108
语文 63
语文 100
语文 81
语文 56
语文 90
现在想把这个表搞成这个样子:
数学 语文
105 63
91 100
81 100
87 56
108 90用SQL怎么实现呢。我用的是MS SQL2005
课目 成绩
数学 105
数学 91
数学 81
数学 87
数学 108
语文 63
语文 100
语文 81
语文 56
语文 90
现在想把这个表搞成这个样子:
数学 语文
105 63
91 100
81 100
87 56
108 90用SQL怎么实现呢。我用的是MS SQL2005
INSERT @t SELECT '数学', 105
UNION ALL SELECT '数学', 91
UNION ALL SELECT '数学', 81
UNION ALL SELECT '数学', 87
UNION ALL SELECT '数学', 108
UNION ALL SELECT '语文', 63
UNION ALL SELECT '语文', 100
UNION ALL SELECT '语文', 81
UNION ALL SELECT '语文', 56
UNION ALL SELECT '语文', 90
SELECT * FROM @t a
FULL JOIN @t b
ON (SELECT COUNT(*) FROM @t WHERE sbj=a.sbj AND id<a.id)=(SELECT COUNT(*) FROM @t WHERE sbj=b.sbj AND id<b.id)
WHERE a.sbj='数学' AND b.sbj='语文'
INSERT into T1 SELECT 1,'A', 105
UNION ALL SELECT 2,'A', 91
UNION ALL SELECT 3,'A', 81
UNION ALL SELECT 4,'A', 87
UNION ALL SELECT 5,'A', 108
UNION ALL SELECT 1,'B', 63
UNION ALL SELECT 2,'B', 100
UNION ALL SELECT 3,'B', 81
UNION ALL SELECT 4,'B', 56
UNION ALL SELECT 5,'B', 90 select * from T1select
sum(isnull(case KM when'A'then FS end,0)) as A,
sum(isnull(case KM when'B'then FS end,0)) as B
from T1
group by XHdrop table T1加了个学号XH
1 A 105
2 A 91
3 A 81
4 A 87
5 A 108
1 B 63
2 B 100
3 B 81
4 B 56
5 B 90
A B
105 63
91 100
81 81
87 56
108 90这是结果
B是语文KM是课目
FS是分数
XH是学号
INSERT into T1 SELECT 1,'A', 105
UNION ALL SELECT 2,'A', 91
UNION ALL SELECT 3,'A', 81
UNION ALL SELECT 4,'A', 87
UNION ALL SELECT 5,'A', 108
UNION ALL SELECT 1,'B', 63
UNION ALL SELECT 2,'B', 100
UNION ALL SELECT 3,'B', 81
UNION ALL SELECT 4,'B', 56
UNION ALL SELECT 5,'B', 90 select * from T1declare @sql varchar(8000)
set @sql='select'
select @sql =@sql+','+''''+rtrim(a.KM)+''''+'=sum(isnull(case KM when'''+rtrim(a.KM)+'''then FS end,0))'
from (select distinct KM from T1) a
set @sql=left(@sql,6)+' '+right(@sql,len(@sql)-7)+' from T1 group by XH'
print @sql
exec(@sql)drop table T1这样
INSERT into T1 SELECT 1,'A', 105
UNION ALL SELECT 2,'A', 91
UNION ALL SELECT 3,'A', 81
UNION ALL SELECT 4,'A', 87
UNION ALL SELECT 5,'A', 108
UNION ALL SELECT 1,'B', 63
UNION ALL SELECT 2,'B', 100
UNION ALL SELECT 3,'B', 81
UNION ALL SELECT 4,'B', 56
UNION ALL SELECT 5,'B', 90
UNION ALL SELECT 1,'C', 63
UNION ALL SELECT 2,'C', 100
UNION ALL SELECT 3,'C', 81
UNION ALL SELECT 4,'C', 56
UNION ALL SELECT 5,'C', 90
UNION ALL SELECT 1,'D', 63
UNION ALL SELECT 2,'D', 100
UNION ALL SELECT 3,'D', 81
UNION ALL SELECT 4,'D', 56
UNION ALL SELECT 5,'D', 90 select * from T1declare @sql varchar(8000)
set @sql='select'
select @sql =@sql+','+''''+rtrim(a.KM)+''''+'=sum(isnull(case KM when'''+rtrim(a.KM)+'''then FS end,0))'
from (select distinct KM from T1) a
set @sql=left(@sql,6)+' '+right(@sql,len(@sql)-7)+' from T1 group by XH'
print @sql
exec(@sql)drop table T1
XH KM FS
1 A 105
2 A 91
3 A 81
4 A 87
5 A 108
1 B 63
2 B 100
3 B 81
4 B 56
5 B 90
1 C 63
2 C 100
3 C 81
4 C 56
5 C 90
1 D 63
2 D 100
3 D 81
4 D 56
5 D 90结果
A B C D
105 63 63 63
91 100 100 100
81 81 81 81
87 56 56 56
108 90 90 90
1 A 105
2 A 91
3 A 81
4 A 87
5 A 108
1 B 63
2 B 100
3 B 81
4 B 56
5 B 90
1 C 63
2 C 100
3 C 81
4 C 56
5 C 90
1 D 63
2 D 100
3 D 81
4 D 56
5 D 90
A B C D
105 63 63 63
91 100 100 100
81 81 81 81
87 56 56 56
108 90 90 90
INSERT @t SELECT '数学', 105 UNION ALL SELECT '数学', 91
UNION ALL SELECT '数学', 81 UNION ALL SELECT '数学', 87
UNION ALL SELECT '数学', 108 UNION ALL SELECT '语文', 63
UNION ALL SELECT '语文', 100 UNION ALL SELECT '语文', 81
UNION ALL SELECT '语文', 56 UNION ALL SELECT '语文', 90 select id=identity(int,1,1),* into #a from @t where sbj='数学'
select id=identity(int,1,1),* into #b from @t where sbj='语文'select a.scr as '数学',b.scr as '语文' from #a a,#b b where a.id=b.id105 63
91 100
81 81
87 56
108 90