如何将横表转为纵表?
0
悬赏园豆：10
[已解决问题]
解决于 2009-12-27 13:21
有如下表记录:
StudentID StudentName CourseName 200901 200902 200903
1 A Chinese 80 85 90
1 A English 85 90 95
要转换成如下格式, 用SQL语句实现,请高手指点, 谢谢!
YearMonth CourseName StudentID StudentName Score
200901 Chinese 1 A 80
200902 Chinese 1 A 85
200903 Chinese 1 A 90
最佳答案
0
CREATE TABLE #A
(
StudentID int,
StudentName varchar(10),
CourseName varchar(20),
S200901 int,
S200902 int,
S200903 int
)
INSERT INTO #A
SELECT 1,'A','Chinese',80,85,90
UNION ALL
SELECT 1,'A','English',85,90,95
---SQL2005SELECT YearMonth,StudentID,StudentName,CourseName,Score FROM
(
SELECT * FROM #A
)s
UNPIVOT
(
Score FOR YearMonth IN([S200901],[S200902],[S200903])
) AS u
---SQL2000
SELECT * FROM
(
SELECT YearMonth='S200901',StudentID,StudentName,CourseName,Score=S200901 FROM #A
UNION ALL
SELECT YearMonth='S200902',StudentID,StudentName,CourseName,Score=S200902 FROM #A
UNION ALL
SELECT YearMonth='S200903',StudentID,StudentName,CourseName,Score=S200903 FROM #A
)t
如上代码就行了，建表时候由于不能用那个200901这样的字段名就在前面加个了S。
效果如下图：
收获园豆：10
其他回答(1)
0
