CODEWITHSUNDEEP
pivotsnowflake-cloud-data-platformunpivotflattenunnest
NLMDEJ
NLMDEJ

Reputation: 395

Unnest from Table in Snowflake

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?

Upvotes: 1

Views: 1352

Answers (2)

Adrian White
Adrian White

Reputation: 1804

You could alternatively use UNPIVOT and NATURAL JOIN.

Above answer is great ... just like thinking about alternative ways of doing things ... you never know when it might suit your needs - plus exposes you to a couple new cool functions.

enter image description here

with cte as (
select
    1 PersonID,
    15 CW_MilesRun,
    25 PW_MilesRun,
    35 CM_MilesRun,
    45 PM_MilesRun
union
select
    2 PersonID,
    10 CW_MilesRun,
    20 PW_MilesRun,
    30 CM_MilesRun,
    40 PM_MilesRun
union
select
    3 PersonID,
    5 CW_MilesRun,
    10 PW_MilesRun,
    15 CM_MilesRun,
    20 PM_MilesRun
)
select * from
(select
   PersonID,
   CW_MilesRun weekly,
   CM_MilesRun monthly
 from
   cte
) unpivot (C_MilesRun for description in (weekly, monthly)) 
natural join 
(select * from
    (select
       PersonID,
       PW_MilesRun weekly,
       PM_MilesRun monthly
     from
       cte
    ) unpivot (P_MilesRun for description in (weekly, monthly))) f

Upvotes: 0

Nat Taylor
Nat Taylor

Reputation: 1108

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.)

Upvotes: 1

Related Questions