我需要转置一个表,其中 column1 是实体的名称,column2 到 column366 是一年中持有美元金额的日期.表格、select语句和输出结果都给出下面 -
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 -
问题 - 这种语法要求我创建一个逗号分隔的列列表 - 基本上是 365 个日期 - 并在 select 语句的 IN 子句中使用该列表.
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.
像这样 -
.....unpivot ("1-1-2020" , "1-2-2020" , "1-3-2020" ......2020 年 12 月 31 日")) 订购 2
.....unpivot (cash for dates in ("1-1-2020" , "1-2-2020" , "1-3-2020"........."12-31-2020")) order by 2
有没有更好的方法呢?像正则表达式一样?我不想以 mm-dd-yyyy 格式输入 365 个日期并为我的麻烦获取心皮隧道
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
这是表格 - 第一行是列标题,第二行是分隔符.第 3、4、5 行是样本数据.
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
我可以使用下面的 select 语句转置它
I can transpose it using the select statement below
从表 1 中选择 *unpivot (("1-1-2020" , "1-2-2020" , "1-3-2020") 日期的现金) 以 2 为单位
select * from Table1 unpivot (cash for dates in ("1-1-2020" , "1-2-2020" , "1-3-2020")) order by 2
获得如下输出 -
Name-------------------dates-----------------------cash
--------------------------------------------------------------
Entity1 01-01-2020 10.00
Entity2 01-01-2020 11.00
Entity3 01-01-2020 112.00
...............................
.............................
.........
and so on
有一种更简单的方法可以在没有 PIVOT 的情况下执行此操作.Snowflake 为您提供了一个将整行表示为对象"的函数——键值对的集合.使用该表示,您可以展平每个元素并提取列名(键 == 日期)和内部值(值 == 现金).这是一个可以执行此操作的查询:
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'
;