Using CROSS APPLY to more easily "UNPIVOT" a wide table in Snowflake?

Question

My company just switched from Microsoft SQL Server to Snowflake, and I have a bunch of scripts where I unpivoted a wide table to a long one using code like this:

SELECT 
    CONGLOM_IDc,
    DESTINATION_IDc,
    COHORTc,
    COHORT_PASS_TYPEc,
    ACCESS_SEASON,
    PASS_TYPE,
    First_Cohortc
FROM #PassTypeByYear2 M
CROSS APPLY
    (
    VALUES
        (CONGLOM_ID, DESTINATION_ID, COHORT, COHORT_PASS_TYPE,'18/19', "18/19_Pass", First_Cohort),
        (CONGLOM_ID, DESTINATION_ID, COHORT, COHORT_PASS_TYPE,'19/20', "19/20_Pass", First_Cohort), 
        (CONGLOM_ID, DESTINATION_ID, COHORT, COHORT_PASS_TYPE,'20/21', "20/21_Pass", First_Cohort),
        (CONGLOM_ID, DESTINATION_ID, COHORT, COHORT_PASS_TYPE,'21/22', "21/22_Pass", First_Cohort),
        (CONGLOM_ID, DESTINATION_ID, COHORT, COHORT_PASS_TYPE,'22/23', "22/23_Pass", First_Cohort)
    ) c (CONGLOM_IDc, DESTINATION_IDc, COHORTc, COHORT_PASS_TYPEc, ACCESS_SEASON, PASS_TYPE, First_Cohortc)

Is there a way to replicate this in Snowflake without using UNPIVOT a bunch of times? I have read the following post, but I can't seem to adjust CROSS JOIN to this situation. Any help is much appreciated.

An example of the "starting" table (#PassTypeByYear2) is below:

CONGLOM_ID	DESTINATION_ID	COHORT	COHORT_PASS_TYPE	18/19_Pass	19/20_Pass	20/21_Pass	21/22_Pass	22/23_Pass	First_Cohort	Consistent_Pass_All_Years
101781679	196	22/23	Paid	Employee	Employee	Employee	Paid	Paid	NO	No
101781679	196	21/22	Paid	Employee	Employee	Employee	Paid	Paid	NO	No
101781679	196	20/21	Employee	Employee	Employee	Employee	Employee	Paid	NO	No
101781679	196	18/19	Employee	Employee	Employee	Employee	Employee	Paid	YES	No
101781679	196	19/20	Employee	Employee	Employee	Employee	Employee	Paid	NO	No
101781679	227	19/20	Employee	NULL	Employee	NULL	NULL	NULL	YES	Yes

and the output I want is below (EDIT: I just realized I did a SELECT TOP 10 * from this table instead of *. It should have a row for every unique cominbation of unpivoted values. I will re-run this query and upload the full table tomorrow - the table is about 5M rows so it takes a long time to run):

CONGLOM_ID	DESTINATION_ID	COHORT	COHORT_PASS_TYPE	ACCESS_SEASON	PASS_TYPE	First_Cohort	Consistent_Pass_All_Years
101781679	196	22/23	Paid	18/19	Employee	NO	No
101781679	196	22/23	Paid	19/20	Employee	NO	No
101781679	196	22/23	Paid	20/21	Employee	NO	No
101781679	196	22/23	Paid	21/22	Paid	NO	No
101781679	196	22/23	Paid	22/23	Paid	NO	No
101781679	196	20/21	Employee	18/19	Employee	NO	No
101781679	196	20/21	Employee	19/20	Employee	NO	No
101781679	196	20/21	Employee	20/21	Employee	NO	No
101781679	196	20/21	Employee	21/22	Paid	NO	No
101781679	196	20/21	Employee	22/23	Paid	NO	No

CROSS APPLY in Oracle/SQL Server substitute in Snowflake

I tried converting the rest of the code to SNOWFLAKE, and I expected that I would end up with a long table (instead of a wide one) where the "18/19_Pass" Column turned into a value in the "18/19" ACCESS_SEASON row. Instead, I am just hitting an error:

SQL Error [2014] [22000]: SQL compilation error: Invalid expression [M.CONGLOM_ID] in VALUES clause

`CROSS APPLY` can be translated to a `LATERAL` join in snowflake. You can unpivot using an inline view that uses `UNION` to produce all five rows. — The Impaler, Jun 27 '23 at 20:32
@TheImpaler I have tried to figure this out - but I just can't wrap my head around how I would actually use the LATERAL join. Do you have an example of how this might work in the context of what I am trying to do? — Eli Rush Kallison, Jun 27 '23 at 20:43
Tags are meant to denote what you are asking *about* @EliRushKallison . You aren't asking about SQL Server here, so the tag is somewhat confusing. — Thom A, Jun 27 '23 at 20:52
Can you update your question (as editable text) with some sample data for your source table(s) and the result you want to achieve, based on that source data? — NickW, Jun 28 '23 at 10:14

score 0 · Answer 1 · answered Jun 29 '23 at 11:08

I think this SQL should give you what you want. I renamed some of the columns so that they were valid identifiers that didn't need to be "quoted" in order to use them - just to make things simpler.

CREATE TABLE PASS_BY_YEAR2 (
CONGLOM_ID TEXT
, DESTINATION_ID TEXT
, COHORT TEXT
, COHORT_PASS_TYPE TEXT
, PASS_18_19 TEXT
, PASS_19_20 TEXT
, PASS_20_21 TEXT
, PASS_21_22 TEXT
, PASS_22_23 TEXT
, FIRST_COHORT TEXT
, CONSISTENT_PASS_ALL_YEARS TEXT);

insert into pass_by_year2 (CONGLOM_ID, DESTINATION_ID, COHORT, COHORT_PASS_TYPE, PASS_18_19, PASS_19_20, PASS_20_21, PASS_21_22, PASS_22_23, First_Cohort, Consistent_Pass_All_Years) values 
('101781679', '196', '22/23', 'Paid', 'Employee', 'Employee', 'Employee', 'Paid', 'Paid', 'NO', 'No'),
('101781679', '196', '21/22', 'Paid', 'Employee', 'Employee', 'Employee', 'Paid', 'Paid', 'NO', 'No'),
('101781679', '196', '20/21', 'Employee', 'Employee', 'Employee', 'Employee', 'Employee', 'Paid', 'NO', 'No'),
('101781679', '196', '18/19', 'Employee', 'Employee', 'Employee', 'Employee', 'Employee', 'Paid', 'YES', 'No'),
('101781679', '196', '19/20', 'Employee', 'Employee', 'Employee', 'Employee', 'Employee', 'Paid', 'NO', 'No'),
('101781679', '227', '19/20', 'Employee', 'NULL', 'Employee', 'NULL', 'NULL', 'NULL', 'YES', 'Yes')
;

SELECT
CONGLOM_ID  
,DESTINATION_ID 
,COHORT 
,COHORT_PASS_TYPE   
,RIGHT(ACCESS_SEASON,LEN(ACCESS_SEASON)-5) ACCESS_SEASON    
,PASS_TYPE  
,First_Cohort   
,Consistent_Pass_All_Years
FROM PASS_BY_YEAR2
UNPIVOT (PASS_TYPE FOR ACCESS_SEASON IN (PASS_18_19, PASS_19_20, PASS_20_21, PASS_21_22, PASS_22_23))
;

THANK YOU! This is great. This will indeed solve the problem for this table. Ultimately, I am trying to figure out how I can UNPIVOT multiple columns at once. This might have been a bad table to use as an example, as I only need to create1 value and 1 name column from the UNPIVOT. But, many of the datasets I use in SQL Server (that I need to transfer to Snowflake) require unpivoting and creating multiple value / name columns. Although I can UNPIVOT the same table multiple times and then join all the unpivoted tables, I was hoping for a more elegant solution. If none exists, this will work! — Eli Rush Kallison, Jun 29 '23 at 17:31

Using CROSS APPLY to more easily "UNPIVOT" a wide table in Snowflake?

1 Answers1