我有一个类似于以下的sql Database表：
@H_502_2@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
我希望它显示如下：种类的交叉表或枢轴
@H_502_2@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函数来完成它，但我更喜欢旧的学校方法：
@H_502_2@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
>我更改了一些列名以避免使用保留字
