Unnest from Table in Snowflake

Question

I have the following table:

PersonID    CW_MilesRun    PW_MilesRun    CM_MilesRun    PM_MilesRun
1           15             25             35             45         
2           10             20             30             40         
3           5              10             15             20         
...

I need to split this table into a vertical table with an id for each field (i.e CD_MilesRun =1, CW_MilesRun = 2, etc) So that my table looks similar to this:

PersonID    TimeID    Description    C_MilesRun    P_MilesRun
1           1         Week           15            25
1           2         Month          35            45
2           1         Week           10            20
2           2         Month          30            40
3           1         Week           5             10
3           2         Month          15            20

In postgres, I would use something similar to:

SELECT
    PersonID
  , unnest(array[1,2]) AS TimeID
  , unnest(array['Week','Month']) AS "Description"
  , unnest(array["CW_MilesRun","CM_MilesRun"]) C_MilesRun
  , unnest(array["PW_MilesRun","PM_MilesRun"]) P_MilesRun
FROM myTableHere
;

However, I cannot get a similar function in snowflake to work. Any ideas?

Answer 1

You can use FLATTEN() with LATERAL to get the result you want, although the query is quite different.

with tbl as (select $1 PersonID, $2   CW_MilesRun, $3    PW_MilesRun, $4   CM_MilesRun, $5    PM_MilesRun from values (1, 15, 25, 35, 45),(2, 10, 20, 30, 40),(3, 5, 10, 15, 20))

select
    PersonID,
    t.value[0] TimeID,
    t.value[1] Description,
    iff(t.index=0,CW_MilesRun,CM_MilesRun) C_MilesRun,
    iff(t.index=1,PW_MilesRun,PM_MilesRun) P_MilesRun
from tbl, lateral flatten(parse_json('[[1, "Week"],[2, "Month"]]')) t;

PERSONID    TIMEID  DESCRIPTION C_MILESRUN  P_MILESRUN
1   1   "Week"  15  25
1   2   "Month" 35  45
2   1   "Week"  10  20
2   2   "Month" 30  40
3   1   "Week"  5   10
3   2   "Month" 15  20

P.S. Use t.* to see what's available after flattening (perhaps that is obvious.)