How to transform to pivot in sql

Question

I have queries like following one

select 
people
,week
,score
from table1
where people in ('a','b','c')

when I execute them, and finally , I get following result

people week score
a      1      1
a      1      2
b      2      2
c      3      3
c      4      4 

But my goal and desired result is pivot by people and week, and the target is sum(score).

    1   2   3   4   5 
a   3   0   0   0   0
b   0   2   0   0   0
c   0   0   0   4   0

I struggled to get this ? How can I get this result ?

If someone has opinion, please let me know.

Thanks

Answer 1

Most SQL databases doesn't support pivot tables. Tim's answer is valid. However, I noticed that you have postgresql in your tags and postgresql support pivoting via the tablefunc extension. This how it's done in postgresql

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT 
    people, 
    COALESCE("1", 0),
    COALESCE("2", 0),
    COALESCE("3", 0),
    COALESCE("4", 0)
FROM CROSSTAB(
    $$
    SELECT people, week, sum(score) 
    FROM table1 
    GROUP BY 1,2
    ORDER BY 1,2
    $$,
    $$
    SELECT DISTINCT(week) FROM table1
    $$
) AS ct(
    people VARCHAR,
    "1" INT,
    "2" INT,
    "3" INT,
    "4" INT
);

Please refer to postgresql's documentation section F.41.1.4. crosstab(text, text) for further information.

Answer 2

Use pivoting logic here:

SELECT
    people,
    COALESCE(SUM(score) FILTER (WHERE week = 1), 0) AS "1",
    COALESCE(SUM(score) FILTER (WHERE week = 2), 0) AS "2",
    COALESCE(SUM(score) FILTER (WHERE week = 3), 0) AS "3",
    COALESCE(SUM(score) FILTER (WHERE week = 4), 0) AS "4",
    COALESCE(SUM(score) FILTER (WHERE week = 5), 0) AS "5"
FROM table1
WHERE people IN ('a', 'b', 'c')
GROUP BY
    people
ORDER BY
    people;

Demo