CODEWITHSUNDEEP
sqlpostgresql
Charan Reddy
Charan Reddy

Reputation: 1

how to merge three rows into one row

how to change output of the sql query from 1 to 2

1:- user_id     subject name     marks 
    1001        maths            67
    1001        pyhsics          78
    1001        chemistry        87
    1002        maths            89
    1002        physics          56
    1002        chemistry        76

2:- user_id     maths      physics     chemistry
    1001        67         78          87
    1002        89         56          76

I'm expecting sql query

Upvotes: 0

Views: 35

Answers (1)

Jim Jones
Jim Jones

Reputation: 19613

Use crosstab (pivot tables) to turn rows into columns:

SELECT *
FROM crosstab(
 'SELECT user_id, subject_name, marks FROM t' 
) AS ct(user_id int, maths int, physics int, chemistry int);

Another option, with a slightly different purpose, is to use conditional aggregates with FILTER:

SELECT 
  user_id,
  SUM(marks) FILTER (WHERE subject_name = 'maths') AS maths,
  SUM(marks) FILTER (WHERE subject_name = 'physics') AS physics,
  SUM(marks) FILTER (WHERE subject_name = 'chemistry') AS chemistry
FROM t
GROUP BY user_id;

Demo: db<>fiddle

Upvotes: 1

Related Questions