SQL Melt after Mapping Columns

Question

I have the following working query:

SELECT
    postgres."week"  AS "week",
    CASE
          WHEN (postgres."spent_money_office") = 1 THEN (postgres."weight")
          WHEN (postgres."spent_time_office") = 1 THEN (postgres."weight")
          WHEN (postgres."spent_other_office") = 1 THEN (postgres."weight")
          ELSE 0
          END  AS "office_leisure",
    CASE
          WHEN (postgres."spent_money_home") = 1 THEN (postgres."weight")
          WHEN (postgres."spent_time_home") = 1 THEN (postgres."weight")
          WHEN (postgres."spent_other_home") = 1 THEN (postgres."weight")
          ELSE 0
          END  AS "home_leisure",
    CASE
          WHEN (postgres."spent_money_vacation") = 1 THEN (postgres."weight")
          WHEN (postgres."spent_time_vacation") = 1 THEN (postgres."weight")
          WHEN (postgres."spent_other_vacation") = 1 THEN (postgres."weight")
          ELSE 0
          END  AS "vacation_leisure"
FROM
    "lake"."postgres" AS "postgres"
GROUP BY
    1,
    2,
    3,
    4
ORDER BY
    1

Which gives 4 columns: week, office_leisure, home_leisure, and vacation_leisure as shown here:

I would like to "melt" the leisure columns into one called leisure_type taking on 3 values, either office/home/vacation and another column called weight. How can this be done using the SQL code query above?

Something like:

week	leisure_type	weight
week 1	office	1.78
week 1	home	1.78
week 1	vacation	1.78
week 1	office	0
...	...	...

Answer 1

You can apply a union over the selection of the week, leisure type and leisure value, for each leisure type you have (so three times):

SELECT week, 
       'office_leisure'   AS leisure_type,
       office_leisure     AS weight 
FROM tab
UNION
SELECT week, 
       'home_leisure'      AS leisure_type,
       home_leisure        AS weight 
FROM tab
UNION
SELECT week, 
       'vacation_leisure'  AS leisure_type,
       vacation_leisure    AS weight 
FROM tab

You can find a fiddle here.