Before we get into that, let's set the scene:
Postgresql crosstab how to#
We could also call this section 'One Limitation of Crosstab and How to Fix It'. Please compare this output with the previous table:Įxample 2: Finding Incomplete Student Records Group by 1,2 order by 1,2') AS final_result(Month TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC) Where evaluation.subject_id = subject.subject_id and student_id = 1 In a grid like the following, the table would look like this: month textįROM crosstab( 'select extract(month from period)::text, subject.name, For instance, suppose we want to obtain the average evaluations for John Smith from March to July. Example 1: Monthly Evaluation AveragesĪs teachers, we may also need a report for a student's evaluation results for the year to date. Let's continue with the teacher-and-class example as we look at a few of our options. Joining all these pieces, our final query will be:įROM crosstab( 'select student, subject, evaluation_result from evaluations order by 1,2')ĪS final_result(Student TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC) Īnd we can see the result in here: Studentįrom a single data set, we can produce many different pivot tables. For our purposes, the final result is defined as:ĪS final_result(Student TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC) We must define the names of the columns and data types that will go into the final result. The crosstab function is invoked in the SELECT statement's FROM clause. SELECT student, subject, evaluation_result FROM evaluations ORDER BY 1,2 In our example, the SELECT parameter will be: If we think of our pivot table as a two-dimensional array, then the first SELECT column is the first array dimension, the second SELECT column is the second dimension, and the third is the array element value.like grid = third_column_value.
These are the evaluation results in our example. The third column in the SELECT represents the value to be assigned to each cell of the pivot table.If the second column returns five different values (geography, history, and so on) the pivot table will have five columns. It is important to note that the values of this column will expand into many columns in the pivot table. In our example, these categories are the school subjects. The second column in the SELECT represents the categories in the pivot table.Notice how students' names (John Smith and Peter Gabriel) appear in the first column.
In our example, this is the student's name.
The crosstab function receives an SQL SELECT command as a parameter, which must be compliant with the following restrictions:
To call the crosstab function, you must first enable the tablefunc extension by executing the following SQL command: If you analyze how the pivot table is built, you will find that we use values from raw data as column headers or field names (in this case, geography, history, maths, etc.) StudentĪs we previously mentioned, the crosstab function is part of a PostgreSQL extension called tablefunc. In computer science, we call this kind of grid a pivot table. The following grid could easily keep track of your students' progress.
0 kommentar(er)
