OUTER APPLY Snowflake-CodePudding

I am trying to apply and OUTERAPPLY function used in SQL to Snowflake. I did some research and found out that you can achieve the same using a LATERAL JOIN, although I am having a hard time grasping how I could achieve the same result.

This is what I want to achieve, create three new columns, for each row in the first table three rows will appear in the new columns, like so:

This is the SQL I have used for far:

OUTER APPLY (VALUES (N'Point', Name, NULL)
                    ,(N'Surname', NULL, Surname)
                    ,(N'Age', Age, NULL)
) S (Point, Column2, Column3)

This is just dummy data so it won't make sense.

CodePudding user response：

It always requires some creativity to convert OUTER APPLY to Snowflake:

with mydata as (
select 'Bob' name, 'Ford' surname, 34 age
union all
select 'Gokhan', 'Atil', 46
)
select name, surname, age, f.VALUE::string Point, 
   CASE Point WHEN 'Name' THEN  Name
              WHEN 'Age' THEN Age::VARCHAR
        ELSE NULL END Column2, 
   IFF( Point = 'Surname', surname, NULL ) Column3 
from mydata
,lateral flatten( split( 'Name,Surname,Age', ',' )) f;

 -------- --------- ----- --------- --------- --------- 
|  NAME  | SURNAME | AGE |  POINT  | COLUMN2 | COLUMN3 |
 -------- --------- ----- --------- --------- --------- 
| Bob    | Ford    |  34 | Name    | Bob     |         |
| Bob    | Ford    |  34 | Surname |         | Ford    |
| Bob    | Ford    |  34 | Age     | 34      |         |
| Gokhan | Atil    |  46 | Name    | Gokhan  |         |
| Gokhan | Atil    |  46 | Surname |         | Atil    |
| Gokhan | Atil    |  46 | Age     | 46      |         |
 -------- --------- ----- --------- --------- ---------