如何将行透视为列(自定义透视) [英] How to pivot rows into columns (custom pivoting)

问题描述

我有一个类似于以下的 Sql 数据库表:

I have a Sql Database table similar to the following:

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

我希望它显示如下:交叉表或枢轴的种类

I would like it displayed as follows: Kind of crosstab or Pivot

Day   P1   P2   P3   P4   P5   P6   P7

Mon   Ch   Ph   Mth  CS2  Lab1 Lab2 Lab3
Tue   Ph   Ele  Hu   Ph   En   CS2  Mth

最理想的方式是什么?有人可以给我看看Sql代码吗?

What would be the ideal way to do it? Can someone please show me the Sql code please?

推荐答案

你或许可以用 PIVOT 函数来做，但我更喜欢老派的方法:

You could probably do it with the PIVOT function, but I prefer the old school method:

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

• 我更改了一些列名以避免保留字

这篇关于如何将行透视为列(自定义透视)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！