sql - 根据列值创建列

Question

我不太热衷于使用 SQL。我被要求创建一个表格，显示学生在类(class)管理系统中每个月可以参加的不同类(class)的能力期望的结果是如下所示的输出:

month       |  math | english
------------+-------+------------
12/1/2014   | 42    | 137   
1/1/2015    | 137   | 76    
2/1/2015    | 139   | 79    
3/1/2015    | 143   | 83    

“数学”和“英语”是course_kind，course_kind 是表course_capacities 中的一列。是否可以根据列中的各种值创建列？

我当前的 SQL 产生了这个结果，这不是最佳的:

 course_kind |   month    | capacity
-------------+------------+----------
 math         | 2015-01-01 |       47
 math         | 2015-02-01 |       43
 math         | 2015-03-01 |       43
 math         | 2015-04-01 |       45
 math         | 2015-05-01 |       46
 math         | 2015-06-01 |       46
 math         | 2015-07-01 |       46
 math         | 2015-08-01 |       46
 math         | 2015-09-01 |       47
 math         | 2015-10-01 |       47
 math         | 2015-11-01 |       47
 math         | 2015-12-01 |       47
 english      | 2015-01-01 |       97
 english      | 2015-01-01 |       73
 english      | 2015-02-01 |       76
 english      | 2015-03-01 |       79
 english      | 2015-04-01 |       83
 english      | 2015-05-01 |       83
 english      | 2015-06-01 |       87
 english      | 2015-07-01 |       89
 english      | 2015-08-01 |       91
 english      | 2015-09-01 |       93
 english      | 2015-10-01 |       97
 english      | 2015-11-01 |       97
 english      | 2015-12-01 |       97

这是 SQL:

SELECT
    course_capacities.course_kind,
    monthly_course_capacities.month,
    monthly_course_capacities.capacity
FROM course_capacities
    INNER JOIN monthly_course_capacities ON course_capacities.id = monthly_course_capacities.course_capacity_id
    ORDER BY course_capacities.course_kind ASC,
    monthly_course_capacities.month ASC;

Answer 1

您可以使用基于案例的聚合，以防 course_kind 列值已知且有限，否则需要创建动态查询。

SELECT
    monthly_course_capacities.month,
    max(case when course_capacities.course_kind ='math' then capacity end ) as math,
    max(case when course_capacities.course_kind ='english' then capacity end ) as english,
FROM course_capacities
INNER JOIN monthly_course_capacities ON course_capacities.id = monthly_course_capacities.course_capacity_id
GROUP BY monthly_course_capacities.month