表转置（输入数据）

+--------+---------+
| col_id | col_val |
+--------+---------+
| axc    | col_x   |
| bdf    | col_f   |
| cde    | col_x   |
| yhc    | col_b   |
| idx    | col_a   |
| dft    | col_y   |
+--------+---------+

用于转换列值的配置单元查询：

SELECT a.col_id,IF(array_contains(collect_list(a.map_values['col_x']),'1'),'Y','N') AS col_x,
IF(array_contains(collect_list(a.map_values['col_y']),'1'),'Y','N') AS col_y,
IF(array_contains(collect_list(a.map_values['col_a']),'1'),'Y','N') AS col_a,
IF(array_contains(collect_list(a.map_values['col_b']),'1'),'Y','N') AS col_b,
IF(array_contains(collect_list(a.map_values['col_f']),'1'),'Y','N') AS col_f FROM (
       SELECT col_id,
              col_val,
              map(col_val, '1') map_values
       FROM   transpose) a GROUP BY a.col_id;

结果

+--------+-------+-------+-------+-------+-------+
| col_id | col_x | col_y | col_a | col_b | col_f |
+--------+-------+-------+-------+-------+-------+
| axc    | Y     | N     | N     | N     | N     |
| bdf    | N     | N     | N     | N     | Y     |
| cde    | Y     | N     | N     | N     | N     |
| dft    | N     | Y     | N     | N     | N     |
| idx    | N     | N     | Y     | N     | N     |
| yhc    | N     | N     | N     | Y     | N     |
+--------+-------+-------+-------+-------+-------+