怎么进行行列转换
我现在有如下表:
班级 语文 数学 英语
1班 80.6 86.0 82.5
2班 80.3 83.5 79.0
3班 84.5 91.0 87.5
行列转换成:
科目 1班 2班 3班
语文 80.6 80.3 84.5
数学 86.0 83.5 91.0
英语 82.5 79.0 87.5
网上别人用sqlserver写的,有点看不懂???
十分感谢
我现在有如下表:
班级 语文 数学 英语
1班 80.6 86.0 82.5
2班 80.3 83.5 79.0
3班 84.5 91.0 87.5
行列转换成:
科目 1班 2班 3班
语文 80.6 80.3 84.5
数学 86.0 83.5 91.0
英语 82.5 79.0 87.5
网上别人用sqlserver写的,有点看不懂???
十分感谢
DECIMAL(5,2)) CHARACTER SET=GBK;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM tb;
Empty set (0.00 sec)mysql> INSERT INTO tb SELECT '1班',80.6,86,82.5;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> INSERT INTO tb SELECT '2班',80.3,83.5,79.0;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> INSERT INTO tb SELECT '3班',84.5,91,87.5;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> SELECT * FROM tb;
+------+-------+-------+-------+
| 班级 | 语文 | 数学 | 英语 |
+------+-------+-------+-------+
| 1班 | 80.60 | 86.00 | 82.50 |
| 2班 | 80.30 | 83.50 | 79.00 |
| 3班 | 84.50 | 91.00 | 87.50 |
+------+-------+-------+-------+
3 rows in set (0.00 sec)mysql> SELECT
-> 科目,
-> MAX(IF(班级='1班',成绩,null)) AS `1班`,
-> MAX(IF(班级='2班',成绩,null)) AS `2班`,
-> MAX(IF(班级='3班',成绩,null)) AS `3班`
-> FROM(
-> SELECT 班级,'语文' AS 科目,语文 AS 成绩 FROM tb
-> UNION ALL
-> SELECT 班级,'数学' AS 科目,数学 AS 成绩 FROM tb
-> UNION ALL
-> SELECT 班级,'英语' AS 科目,英语 AS 成绩 FROM tb
-> ) AS A
-> GROUP BY 科目;
+------+-------+-------+-------+
| 科目 | 1班 | 2班 | 3班 |
+------+-------+-------+-------+
| 数学 | 86.00 | 83.50 | 91.00 |
| 英语 | 82.50 | 79.00 | 87.50 |
| 语文 | 80.60 | 80.30 | 84.50 |
+------+-------+-------+-------+
3 rows in set (0.00 sec)
create table test1(A varchar(20),b int,c int,d int,e int)
insert into test1 select 'x',1,2 ,3 ,4
insert into test1 select 'y',5,6 ,7 ,8
insert into test1 select 'z',9,10,11,12
--生成中间数据表
declare @s varchar(8000)
set @s='create table test2(a varchar(20)'
select @s=@s+','+A+' varchar(10)' from test1
set @s=@s+')'
exec(@s)
--借助中间表实现行列转换
declare @name varchar(20)
declare t_cursor cursor for
select name from syscolumns
where id=object_id('test1') and colid>1 order by colid
open t_cursor
fetch next from t_cursor into @name
while @@fetch_status=0
begin
exec('select '+@name+' as t into test3 from test1')
set @s='insert into test2 select '''+@name+''''
select @s=@s+','''+rtrim(t)+'''' from test3
exec(@s)
exec('drop table test3')
fetch next from t_cursor into @name
end
close t_cursor
deallocate t_cursor
能否用这种方法实现???