1

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

2

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.

  • 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. – tr3online Nov 7 '19 at 16:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.