Snowflake unpivoting

Question

I need to transpose a table in which column1 is name of an entity and column2 to column366 are dates in a year that hold a dollar amount. The table, the select statement and the output result are all given below -

Question - This syntax requires me to create a comma separated list of columns - which are basically 365 dates - and use that list in the IN clause of the select statement.

Like this -

.....unpivot (cash for dates in ("1-1-2020" , "1-2-2020" , "1-3-2020"........."12-31-2020")) order by 2

Is there any better way of doing this ? Like with regular expressions ? I don't want to type 365 dates in mm-dd-yyyy format and get carpel tunnel for my trouble

Here is the table - First line is column header, second line is separator. 3rd, 4th and 5th lines are sample data.

Name                  01-01-2020            01-02-2020          01-03-2020           12-31-2020
---------------------------------------------------------------------------------------------------
Entity1               10.00                 15.75               20.00                100.00
Entity2               11.00                 16.75               20.00                 10.00
Entity3              112.00                166.75               29.00                108.00

I can transpose it using the select statement below

select * from Table1 unpivot (cash for dates in ("1-1-2020" , "1-2-2020" , "1-3-2020")) order by 2

to get an output like the one below -

    Name-------------------dates-----------------------cash
    --------------------------------------------------------------
    Entity1                01-01-2020                   10.00
    Entity2                01-01-2020                   11.00
    Entity3                01-01-2020                  112.00

...............................
.............................
.........
and so on

Two questions: do you want a column for every date, regardless of whether it is in the data? and have you looked at creating a stored procedure that dynamically generates the statement for you? — Mike Walton, Jun 10 '20 at 21:54

score 4 · Answer 1 · answered Jun 11 '20 at 00:13

There is a simpler way to do this without PIVOT. Snowflake gives you a function to represent an entire row as an "OBJECT" -- a collection of key-value pairs. With that representation, you can FLATTEN each element and extract both the column name (key == date) and the value inside (value == cash). Here is a query that will do it:

with obj as (
   select OBJECT_CONSTRUCT(*) o from Table1
)
select o:NAME::varchar as name, 
       f.key::date as date, 
       f.value::float as cash  
from obj,
     lateral flatten (input => obj.o, mode => 'OBJECT') f
where f.key != 'NAME'
;

Snowflake unpivoting

1 Answers1

Linked