openpolitics.com

Creating Pivot Tables in PostgreSQL Using the Crosstab Function

SELECT *

FROM crosstab( 'select extract(month from period)::text, subject.name,

             trunc(avg(evaluation_result),2)

     from evaluation, subject 

     where evaluation.subject_id = subject.subject_id and student_id = 1

     group by 1,2 order by 1,2')  AS final_result(Month TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);