SQL | Select rows as columns

Question

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.

score 1 · Accepted Answer · answered Jun 05 '23 at 12:51

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.

SQL | Select rows as columns

1 Answers1