PostgreSQL cross-tab sample

gistfile1.sql

-- -------------------------------
-- MySQL Only
-- -------------------------------
with grade_calculation as (
    select '00001' student_code, 'N001' course_code, 90 grade union all
    select '00001' student_code, 'N002' course_code, 95 grade union all
    select '00001' student_code, 'N003' course_code, 80 grade union all
    select '00001' student_code, 'N004' course_code, 75 grade union all
    select '00002' student_code, 'N001' course_code, 70 grade union all
    select '00002' student_code, 'N002' course_code, 80 grade union all
    select '00002' student_code, 'N003' course_code, 50 grade union all
    select '00003' student_code, 'N001' course_code, 30 grade union all
    select '00003' student_code, 'N002' course_code, 70 grade union all
    select '00003' student_code, 'N003' course_code, 80 grade union all
    select '00004' student_code, 'N001' course_code, 75 grade union all
    select '00004' student_code, 'N005' course_code, 95 grade union all
    select '00005' student_code, 'N002' course_code, 85 grade union all
    select '00005' student_code, 'N003' course_code, 55 grade
)
select student_code
, sum(coalesce(case when course_code='N001' then grade else 0 end, 0)) N001
, sum(coalesce(case when course_code='N002' then grade else 0 end, 0)) N002
, sum(coalesce(case when course_code='N003' then grade else 0 end, 0)) N003
from grade_calculation
group by student_code;


-- -------------------------------
-- create table student_grades
-- -------------------------------
create table student_grades as
    select '00001' student_code, 'N001' course_code, 90 grade union all
    select '00001' student_code, 'N002' course_code, 95 grade union all
    select '00001' student_code, 'N003' course_code, 80 grade union all
    select '00001' student_code, 'N004' course_code, 75 grade union all
    select '00002' student_code, 'N001' course_code, 70 grade union all
    select '00002' student_code, 'N002' course_code, 80 grade union all
    select '00002' student_code, 'N003' course_code, 50 grade union all
    select '00003' student_code, 'N001' course_code, 30 grade union all
    select '00003' student_code, 'N002' course_code, 70 grade union all
    select '00003' student_code, 'N003' course_code, 80 grade union all
    select '00004' student_code, 'N001' course_code, 75 grade union all
    select '00004' student_code, 'N005' course_code, 95 grade union all
    select '00005' student_code, 'N002' course_code, 85 grade union all
    select '00005' student_code, 'N003' course_code, 55 grade;

-- -------------------------------
-- simple crosstab
-- -------------------------------
select student_code
, sum(coalesce(case when course_code='N001' then grade else 0 end, 0)) N001
, sum(coalesce(case when course_code='N002' then grade else 0 end, 0)) N002
, sum(coalesce(case when course_code='N003' then grade else 0 end, 0)) N003
from student_grades
group by student_code;

-- -------------------------------
-- dynamic crosstab
-- -------------------------------
set @sql = null;
select group_concat(distinct
    concat('sum(coalesce(case when course_code=''', course_code, ''' then grade else 0 end, 0)) ', course_code)
)
into @sql
from student_grades;
set @sql = concat('
    SELECT student_code, ', @sql, '
    FROM student_grades
    GROUP BY student_code');

prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

-- -------------------------------
-- PostgreSQL Only
-- -------------------------------
create extension tablefunc with schema public;

-- -------------------------------
-- create table student_grades
-- -------------------------------
create table student_grades as
    select '00001' student_code, 'N001' course_code, 90 grade union all
    select '00001' student_code, 'N002' course_code, 95 grade union all
    select '00001' student_code, 'N003' course_code, 80 grade union all
    select '00001' student_code, 'N004' course_code, 75 grade union all
    select '00001' student_code, 'N005' course_code, 0 grade union all
    select '00002' student_code, 'N001' course_code, 70 grade union all
    select '00002' student_code, 'N002' course_code, 80 grade union all
    select '00002' student_code, 'N003' course_code, 50 grade union all
    select '00002' student_code, 'N004' course_code, 0 grade union all
    select '00002' student_code, 'N005' course_code, 0 grade union all
    select '00003' student_code, 'N001' course_code, 30 grade union all
    select '00003' student_code, 'N002' course_code, 70 grade union all
    select '00003' student_code, 'N003' course_code, 80 grade union all
    select '00003' student_code, 'N004' course_code, 0 grade union all
    select '00003' student_code, 'N005' course_code, 0 grade union all
    select '00004' student_code, 'N001' course_code, 75 grade union all
    select '00004' student_code, 'N002' course_code, 0 grade union all
    select '00004' student_code, 'N003' course_code, 0 grade union all
    select '00004' student_code, 'N004' course_code, 0 grade union all
    select '00004' student_code, 'N005' course_code, 95 grade union all
    select '00005' student_code, 'N001' course_code, 0 grade union all
    select '00005' student_code, 'N002' course_code, 85 grade union all
    select '00005' student_code, 'N003' course_code, 55 grade union all
    select '00005' student_code, 'N004' course_code, 0 grade union all
    select '00005' student_code, 'N005' course_code, 0 grade;

-- -------------------------------
-- dynamic crosstab
-- -------------------------------
select *
from crosstab('
    select *
    from student_grades
    order by student_code, course_code')
AS ct(
  student_code text, 
  course1 int, 
  course2 int, 
  course3 int, 
  course4 int, 
  course5 int
);

Contoh Output

+--------------+------+------+------+
| student_code | N001 | N002 | N003 |
+--------------+------+------+------+
| 00001        |   90 |  100 |   80 |
| 00002        |   70 |   80 |   50 |
| 00003        |   30 |   70 |   80 |
| 00004        |   75 |    0 |    0 |
| 00005        |    0 |   85 |   55 |
+--------------+------+------+------+
5 rows in set (0.014 sec)