Search code examples
sqlpostgresqlgroup-by

SQL | Select rows as columns

I have a table schema as below:

Student ID Subject Marks
1 Maths 10
1 Physics 30
2 Maths 25

I want to select the values in below format:

Student ID Maths Physics
1 10 30
2 25

Could you please suggest how this can be accomplished in SQL? I'm using Postgresql.

Or I'll have to do it in Java as that is our middleware?

Thank you.

Solution

  • You can use crosstab function which belongs to the tablefunc module. First, download the module.

    After you can use this query (necessary inserts can be found here):

    SELECT *
FROM crosstab(
    'select student_id, subject, mark::text 
     from student_marks
     order by student_id ASC, mark ASC'
) AS ct(entity_id integer, "Math" text, "Physics" text);

    Here crosstab receives 1 argument, a SQL query as a text, and transposes the query's result into these columns: entity_id, Math and Physics.

    You can find out more about the crosstab here: one, two.