CASE WHEN ISSUE: same part of condition for multiple conditions

Question

I have some paths that someone needs to trail depending on their choices: default, Back, Front, Mobile.

If a person chooses Back it must trail default path + back path.

The table should be like that:

CODE     PATH
A1       DEFAULT
A2       DEFAULT
A3       DEFAULT
B1       BACK
B2       BACK
D3       BACK
C2       FRONT
C4       FRONT
E2       MOBILE
E5       MOBILE

The result I need using case when (it must be case when because i'm querying in Google Data Studio, so I cannot use the where clause) is the following:

CODE     PATH
A1       BACK
A2       BACK
A3       BACK
B1       BACK
B2       BACK
D3       BACK
A1       FRONT
A2       FRONT
A3       FRONT
C2       FRONT
C4       FRONT
A1       MOBILE
A2       MOBILE
A3       MOBILE
E2       MOBILE
E5       MOBILE

I tried this query but it didn't work.

CASE
    WHEN PATH IN ('DEFAULT', 'BACK') THEN 'BACK'
    WHEN PATH IN ('DEFAULT', 'FRONT') THEN 'FRONT'
    WHEN PATH IN ('DEFAULT, ' MOBILE') THEN 'MOBILE'
ELSE NULL
END

Any clue?

Answer 1

You will need either

SELECT code, 'BACK' AS path FROM example WHERE path IN ('DEFAULT', 'BACK')
UNION ALL
SELECT code, 'FRONT' AS path FROM example WHERE path IN ('DEFAULT', 'FRONT')
UNION ALL
SELECT code, 'MOBILE' AS path FROM example WHERE path IN ('DEFAULT', 'MOBILE')

or

SELECT
  code,
  UNNEST(CASE path
    WHEN 'DEFAULT' THEN ARRAY['BACK', 'FRONT', 'MOBILE']
    ELSE ARRAY[path]
  END) AS path
FROM example

or (maybe the nicest)

SELECT
  code,
  trail.result
FROM example
CROSS JOIN (VALUES
  ('DEFAULT', 'BACK'),
  ('BACK', 'BACK'),
  ('DEFAULT', 'FRONT'),
  ('FRONT', 'FRONT'),
  ('DEFAULT', 'MOBILE'),
  ('MOBILE', 'MOBILE')
) AS trail(path, result) USING (path)

You cannot achieve this duplication of rows with only a CASE expression.