婚介网站模板,千库网ppt,北京私人做网站,阿里云学生wordpress查了很多资料发现网上很多文章都是转发和抄袭#xff0c;有些问题。这里分享一个自己项目中使用的行转列例子#xff0c;供大家参考。代码如下#xff1a;
SELECTmy_id,nm_cd_map[A] AS my_cd_a,nm_cd_map[B] AS my_cd_b,nm_cd_map[C] AS my_cd_c,nm_num_map[A] AS my_num_…查了很多资料发现网上很多文章都是转发和抄袭有些问题。这里分享一个自己项目中使用的行转列例子供大家参考。代码如下
SELECTmy_id,nm_cd_map[A] AS my_cd_a,nm_cd_map[B] AS my_cd_b,nm_cd_map[C] AS my_cd_c,nm_num_map[A] AS my_num_a,nm_num_map[B] AS my_num_b,nm_num_map[C] AS my_num_c
FROM(SELECTt.my_id,STR_TO_MAP(my_nm_cds,;,:) AS nm_cd_map,STR_TO_MAP(my_nm_nums,;,:) AS nm_num_mapFROM(SELECTmy_id,CONCAT_WS(;,COLLECT_LIST(CONCAT(my_nm,:,my_cd))) AS my_nm_cds,CONCAT_WS(;,COLLECT_LIST(CONCAT(my_nm,:,my_num))) AS my_nm_numsFROM(SELECT 1 AS my_id,A AS my_nm,D01 AS my_cd,19 AS my_numUNION ALLSELECT 1 AS my_id,B AS my_nm,D04 AS my_cd,18 AS my_numUNION ALLSELECT 1 AS my_id,C AS my_nm,D02 AS my_cd,17 AS my_numUNION ALLSELECT 2 AS my_id,A AS my_nm,D03 AS my_cd,16 AS my_numUNION ALLSELECT 2 AS my_id,B AS my_nm,D05 AS my_cd,15 AS my_numUNION ALLSELECT 2 AS my_id,C AS my_nm,D06 AS my_cd,14 AS my_num)GROUP BY my_id) t) t
WHERE 11;如果是在SparkSQL或Presto平台或者阿里云的MaxCompute平台还可使用如下方式
-- 其实也可使用CONCAT然后STR_TO_MAP的方式或者用MAP_FROM_ARRAYS再或者用数组排序后ARRAY[n] AS的方式
SELECTmy_id,nm_cd_map[A] AS my_cd_a,nm_cd_map[B] AS my_cd_b,nm_cd_map[C] AS my_cd_c,nm_num_map[A] AS my_num_a,nm_num_map[B] AS my_num_b,nm_num_map[C] AS my_num_c
FROM(SELECTt.my_id,MAP_FROM_ENTRIES(COLLECT_LIST(nm_cd)) AS nm_cd_map,MAP_FROM_ENTRIES(COLLECT_LIST(nm_num)) AS nm_num_mapFROM(SELECTmy_id,my_nm,my_cd,my_num,STRUCT(my_nm,my_cd) AS nm_cd,STRUCT(my_nm,my_num) AS nm_numFROM(SELECT 1 AS my_id,A AS my_nm,D01 AS my_cd,19 AS my_numUNION ALLSELECT 1 AS my_id,B AS my_nm,D04 AS my_cd,18 AS my_numUNION ALLSELECT 1 AS my_id,C AS my_nm,D02 AS my_cd,17 AS my_numUNION ALLSELECT 2 AS my_id,A AS my_nm,D03 AS my_cd,16 AS my_numUNION ALLSELECT 2 AS my_id,B AS my_nm,D05 AS my_cd,15 AS my_numUNION ALLSELECT 2 AS my_id,C AS my_nm,D06 AS my_cd,14 AS my_num)) tGROUP BY my_id) t
WHERE 11;
