没办法。不知道为什么没有可用分.*_*
说明:SQL 列与行的转换.
现在有数据:
stuName AAA BBB CCC
-------------------- ----------- ----------- -----------
1Ming 80 60 70
2Hua 55 85 91
3Jin 86 83 75
我要把上面的转换成下面的
而不是从下面转成上面的
[id可以随便 或是+1哟]
id------name----subject---grade
-------------------------------
1 1Ming AAA 80
2 1Ming BBB 60
3 1Ming CCC 70
4 2Hua AAA 55
5 2Hua BBB 85
6 2Hua CCC 91
7 3Jin AAA 86
8 3Jin BBB 83
9 3Jin CCC 75谢谢
说明:SQL 列与行的转换.
现在有数据:
stuName AAA BBB CCC
-------------------- ----------- ----------- -----------
1Ming 80 60 70
2Hua 55 85 91
3Jin 86 83 75
我要把上面的转换成下面的
而不是从下面转成上面的
[id可以随便 或是+1哟]
id------name----subject---grade
-------------------------------
1 1Ming AAA 80
2 1Ming BBB 60
3 1Ming CCC 70
4 2Hua AAA 55
5 2Hua BBB 85
6 2Hua CCC 91
7 3Jin AAA 86
8 3Jin BBB 83
9 3Jin CCC 75谢谢
INSERT INTO tblRowColumn
SELECT 1,'1Ming',80,60,70 UNION ALL
SELECT 2,'2Hua',55,85,91 UNION ALL
SELECT 3,'3Jin',86,83,75
更改一下:
create TABLE tblColumnRow(id int,stuName varchar(20) ,AAA int,BBB int,CCC int)
INSERT INTO tblColumnRow
SELECT 1,'1Ming',80,60,70 UNION ALL
SELECT 2,'2Hua',55,85,91 UNION ALL
SELECT 3,'3Jin',86,83,75
INSERT INTO tblColumnRow
SELECT 1,'1Ming',80,60,70 UNION ALL
SELECT 2,'2Hua',55,85,91 UNION ALL
SELECT 3,'3Jin',86,83,75 SELECT ID AS ID,stuName AS stuName,'AAA' AS SUBJECT,AAA AS GRADE FROM tblColumnRow
UNION ALL
SELECT ID AS ID,stuName AS stuName,'BBB' AS SUBJECT,BBB AS GRADE FROM tblColumnRow
UNION ALL
SELECT ID AS ID,stuName AS stuName,'CCC' AS SUBJECT,CCC AS GRADE FROM tblColumnRow
ORDER BY stuName,SUBJECTDROP TABLE tblColumnRow
/*
ID stuName SUBJECT GRADE
----------- -------------------- ------- -----------
1 1Ming AAA 80
1 1Ming BBB 60
1 1Ming CCC 70
2 2Hua AAA 55
2 2Hua BBB 85
2 2Hua CCC 91
3 3Jin AAA 86
3 3Jin BBB 83
3 3Jin CCC 75
*/
我搜索一下行列转换
都是从下面转成上面的
倒转的没有找到。。