Postgres Crosstab allocating values to wrong columns

Question

I have a sample table named antest as shown below to test the crosstab function.

create table antest(student text, subject text, result numeric);
insert into antest(student, subject, result) values
('peter','music',2.0), 
('peter','language',2.0),
('gabriel','history',8.0),
('john','history',9.0),
('john','maths',4.0),
('john','music',7.0);

student|subject|result
-------+-------+------
peter  |music  |2.0
peter  |lanuage|2.0
gabriel|history|8.0
john   |history|9.0
john   |maths  |4.0
john   |music  |7.0

Result wanted:

student|music|language|history|maths
-------+-----+--------+-------+-----
peter  |2.0  |2.0     |       |
gabriel|     |        |8.0    |
john   |7.0  |        |9.0    |4.0

I have executed the follwoin query for that:

select * 
from public.crosstab (
    'select student, subject, result from antest',
    'select distinct subject from antest'
) as final_result(student text, music numeric, maths numeric, history numeric, language numeric);

I got the following result:

student|music|maths|history|language
-------+-----+-----+-------+--------
peter  |2.0  |     |       |2.0
gabriel|     |8.0  |       |
john   |7.0  |9.0  |4.0    |

Kindly let me know the mistake I am doing.

I have to repeat this query for anyother database which is 30gb large later with about 75 attributes. Is there any possibility to automate it?

Answer 1

You have to take care that the order of categories is exactly the same in the category query and in the column definition. Because you want an arbitrary chosen (not alphabetical) order, you should use values in the category query.

select * 
from crosstab (
    $q$ select student, subject, result from antest $q$,
    $q$ values ('music'), ('language'), ('history'), ('maths') $q$
) as final_result(student text, music numeric, language numeric, history numeric, maths numeric);


 student | music | language | history | maths 
---------+-------+----------+---------+-------
 peter   |   2.0 |      2.0 |         |      
 gabriel |       |          |     8.0 |      
 john    |   7.0 |          |     9.0 |   4.0
(3 rows)

Of course, you can use order by in the query but then you have to change the order in the column definition:

select * 
from crosstab (
    $q$ select student, subject, result from antest $q$,
    $q$ select distinct subject from antest order by 1 $q$
) as final_result(student text, history numeric, language numeric, math numeric, music numeric);

 student | history | language | math | music 
---------+---------+----------+------+-------
 peter   |         |      2.0 |      |   2.0
 gabriel |     8.0 |          |      |      
 john    |     9.0 |          |  4.0 |   7.0
(3 rows)