Multidimensional pivot in postgres without crosstabs I have a bunch of subqueries that I've used to get the following data structure (that I've made in a table for brevity):

CREATE TABLE newtable (
    id int8 NULL,
    start_of_week varchar NULL,
    dow varchar NULL,
    hours int NULL
);



and data:

INSERT INTO newtable
(id, start_of_week, dow, hours)
VALUES (1677200432956441811, '2019-10-20', 'friday   ', 5),
('1677200432956441811', '2019-10-20', 'thursday ', 5),
('1677200432956441811', '2019-10-27', 'tuesday  ', 10),
('1677200432956441811', '2019-10-27', 'wednesday', 10),
('1677200432956441811', '2019-10-27', 'sunday   ', 5),
('1677200432956441811', '2019-10-27', 'monday   ', 10),
('1677200432956441811', '2019-10-27', 'thursday ', 10),
('1684371316653688689', '2019-10-27', 'thursday ', 4),
('1684371316653688689', '2019-10-27', 'wednesday', 4),
('1684371316653688689', '2019-10-27', 'saturday ', 3),
('1684371316653688689', '2019-10-27', 'friday   ', 3),
('1684371316653688689', '2019-11-03', 'sunday   ', 3),
('1700349277961717472', '2019-10-27', 'tuesday  ', 1),
('1700349277961717472', '2019-10-27', 'wednesday', 1),
('1700349277961717472', '2019-10-27', 'thursday ', 1),
('1700440362062972711', '2019-10-27', 'tuesday  ', 8),
('1700440362062972711', '2019-10-27', 'monday   ', 8);


Now I would like to be able to pivot this table on id and start_of_week so that the output would ideally be:

id                  | start_of_week | sunday | monday | tuesday | wednesday | thursday | friday | saturday 
1677200432956441811 | 2019-10-20    | null   | null   | null    | null      | 5        | 5      | null
1677200432956441811 | 2019-10-27    | 5      | 10     | 10      | 10        | 10       | null   | null
etc...


Ideally I'd like to do this without crosstabs.

I've tried to use:

select 
    production_id,
    start_of_week,
    (CASE WHEN dow='sunday' THEN hours END) "sunday"
    ...


But for some reason the resultant only renders null.

Here is a db-fiddle link to the aforementioned
I think you're after something like this:

  SELECT id,
         start_of_week,
         sum(hours) as "Overall",
         sum(hours) filter (where "dow" = 'sunday   ') as "sunday   ",
         sum(hours) filter (where "dow" = 'monday   ') as "monday   ",
         sum(hours) filter (where "dow" = 'tuesday  ') as "tuesday  ",
         sum(hours) filter (where "dow" = 'wednesday') as "wednesday",
         sum(hours) filter (where "dow" = 'thursday ') as "thursday ",
         sum(hours) filter (where "dow" = 'friday   ') as "friday   ",
         sum(hours) filter (where "dow" = 'saturday ') as "saturday "

    FROM newtable

   WHERE id is not null

GROUP BY id,
         start_of_week

ORDER BY start_of_week;


I added an overall sum as well.