DW(数仓)和RDBS的行转列，列转行总结

RDBS（MySQL）的行转列和列转行

行转列

-- 使用GROUP_CONCAT来获得，每个人考了哪些科目以及各科的得分
SELECt   name
        ,GROUP_CONCAt(class) AS class_list
        ,GROUP_CONCAt(score) AS score_list
FROM t20220302
GROUP BY  name;

列转行

-- 在将数据恢复到之前的
WITH tmp AS
(
  SELECt   name
          ,GROUP_CONCAt(class) AS class_list
          ,GROUP_CONCAt(score) AS score_list
  FROM t20220302
  GROUP BY name
), tmp2 AS
(
  SELECt   name
          ,SUBSTRING_INDEX(class_list,",",1) class
          ,SUBSTRING_INDEX(score_list,",",1) score
  FROM tmp
  UNIOn
  SELECt   name
          ,SUBSTRING_INDEX(SUBSTRING_INDEX(class_list,",",2),",",-1) class
          ,SUBSTRING_INDEX(SUBSTRING_INDEX(score_list,",",2),",",-1) score
  FROM tmp
  UNIOn
  SELECt   name
          ,SUBSTRING_INDEX(SUBSTRING_INDEX(class_list,",",3),",",-1) class
          ,SUBSTRING_INDEX(SUBSTRING_INDEX(score_list,",",3),",",-1) score
  FROM tmp
  UNIOn
  SELECt   name
          ,SUBSTRING_INDEX(SUBSTRING_INDEX(class_list,",",4),",",-1) class
          ,SUBSTRING_INDEX(SUBSTRING_INDEX(score_list,",",4),",",-1) score
  FROM tmp
)
SELECt *
FROM tmp2;

DW（hive数仓）的行转列和列转行

行转列

-- hive的行转列
SELECt   name
        ,COLLECT_LIST(class) class_list
        ,COLLECT_LIST(score) score_list
FROM t20220302
GROUP BY  name;

列转行 

-- hive的列转行
-- 为了实现途中的结果，需要分别爆炸两次，然后关联才能得到最终的结果
WITH explode_table1 AS
(
    SELECt   name
            ,tmp1.class
    FROM test1
    LATERAL VIEW EXPLODE(SPLIT(class_list,",")) tmp1 AS class
), explode_table2 AS
(
    SELECt   name
            ,tmp2.score
            ,ROW_NUMBER() OVER() flag
    FROM test1
    LATERAL VIEW EXPLODE(SPLIT(score_list,",")) tmp2 AS score
)
SELECt   t1.name
        ,t1.class
        ,t2.score
FROM explode_table1 t1
LEFT JOIN explode_table2 t2
    ON t1.flag = t2.flag
;

我的这个答案不够严谨, 效率也不高，大佬们如果有更好的方法请私信我