Multidimensional pivot in postgres without crosstabs

Question

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

Answer 1

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.

• tr3online: Thanks. That's precisely what was hoping for. Apparently I need to trim() the toChar() that was generating the days of the week. I didn't realize there was whitespace until your solution.