我有一个类似于以下的sql Database表：
Day Period Subject Mon 1 Ch Mon 2 Ph Mon 3 Mth Mon 4 CS Mon 5 Lab1 Mon 6 Lab2 Mon 7 Lab3 Tue 1 Ph Tue 2 Ele Tue 3 Hu Tue 4 Ph Tue 5 En Tue 6 CS2 Tue 7 Mth
我希望它显示如下：种类的交叉表或枢轴
Day P1 P2 P3 P4 P5 P6 P7 Mon Ch Ph Mth CS2 Lab1 Lab2 Lab3 Tue Ph Ele Hu Ph En CS2 Mth
解决方法
您可以使用PIVOT函数来完成它，但我更喜欢旧的学校方法：
SELECT
dy,MAX(CASE WHEN period = 1 THEN subj ELSE NULL END) AS P1,MAX(CASE WHEN period = 2 THEN subj ELSE NULL END) AS P2,MAX(CASE WHEN period = 3 THEN subj ELSE NULL END) AS P3,MAX(CASE WHEN period = 4 THEN subj ELSE NULL END) AS P4,MAX(CASE WHEN period = 5 THEN subj ELSE NULL END) AS P5,MAX(CASE WHEN period = 6 THEN subj ELSE NULL END) AS P6,MAX(CASE WHEN period = 7 THEN subj ELSE NULL END) AS P7
FROM
Classes
GROUP BY
dy
ORDER BY
CASE dy
WHEN 'Mon' THEN 1
WHEN 'Tue' THEN 2
WHEN 'Wed' THEN 3
WHEN 'Thu' THEN 4
WHEN 'Fri' THEN 5
WHEN 'Sat' THEN 6
WHEN 'Sun' THEN 7
ELSE 8
END
>我更改了一些列名以避免使用保留字
