Snowflake中逆透视时将列名转为值并实现拼接的方法

样本数据 #

col_1 | col_2 | val_1 | val_2
----------------------------
TPEB  | SMB   | 12.0  | 12.7
FEWB  | ENT   | 13    | 10

预期输出 #

预期输出

dimension     | dimension_value | val_1 | val_2
[col_1,col_2] | [TPEB,SMB]      | 12.0  | 12.7
[col_1,col_2] | [FEWB,ENT]      | 13    | 10

你尝试的方法及结果 #

你使用逆透视代码：

select *
from table
unpivot (slice_value for slice_name in (col_1, col_2));

得到输出：

slice_name | slice_value | val_1 | val_2
col_1      | TPEB        | 12.0  | 12.7
col_2      | SMB         | 12.0  | 12.7
col_1      | FEWB        | 13    | 10
col_2      | ENT         | 13    | 10

解决方案 #

不需要使用逆透视，直接构造数组并格式化字符串即可实现目标：

方法一：利用Snowflake数组默认格式 #

select
  '[col_1,col_2]' as dimension,
  ARRAY_CONSTRUCT(col_1, col_2) as dimension_value,
  val_1,
  val_2
from your_table;

方法二：手动拼接严格匹配格式（适用于需要去掉字符串元素引号的场景） #

select
  '[col_1,col_2]' as dimension,
  '[' || ARRAY_TO_STRING(ARRAY_CONSTRUCT(col_1, col_2), ',') || ']' as dimension_value,
  val_1,
  val_2
from your_table;

说明：

• dimension列直接硬编码固定值[col_1,col_2]，因为这是固定的列名组合
• ARRAY_CONSTRUCT(col_1, col_2)将每行的col_1和col_2值组合成数组，Snowflake默认输出带方括号的格式；如果列值是字符串类型，默认会带引号，此时用ARRAY_TO_STRING可以拼接成不带引号的格式

内容的提问来源于stack exchange，提问作者jay