CODEWITHSUNDEEP
sqlpostgresqlpivot
Jared Rader
Jared Rader

Reputation: 880

creating columns based on column values

I don't work with SQL too intensely. I've been asked to create a table that shows the capacities of students different courses can take on each month in a course management system The desired result is an output that looks like this:

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

"Math" and "English" are course_kinds, course_kind is a column on the table course_capacities. Is it possible to create columns based on the various values in a column?

My current SQL produces this result, which is less than optimal:

 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

Here's the 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;

Upvotes: 1

Views: 96

Answers (1)

radar
radar

Reputation: 13425

you can use case based aggregation, in case course_kind column values are known and are limited, other wise, need to create a dynamic query.

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

Upvotes: 4

Related Questions