全部产品
云市场

行列转换

更新时间:2019-04-11 10:26:46

行转列

方法一:通过CASE WHEN语句

  1. SELECT
  2. username,
  3. max(CASE WHEN subject = '语文' THEN score END) AS `语文`,
  4. max(CASE WHEN subject = '数学' THEN score END) AS `数学`,
  5. max(CASE WHEN subject = '英语' THEN score END) AS `英语`
  6. FROM pivot
  7. GROUP BY username
  8. ORDER BY username;

结果:

行列转换方法1

方法二:通过map_agg函数

通过map_agg函数实现行转列需要以下两个步骤:

  1. 通过map_agg函数把两个列的多行的值,映射为map。

    1. SELECT username, map_agg(subject, score) kv
    2. FROM pivot
    3. GROUP BY username
    4. ORDER BY username;

    结果:

    map_agg实现效果

  2. 通过map的输出,达到多列输出的目的。

最终,通过map_agg函数实现行转列的详细SQL为:

  1. SELECT
  2. username,
  3. if(element_at(kv, '语文') = null, null, kv['语文']) AS `语文`,
  4. if(element_at(kv, '数学') = null, null, kv['数学']) AS `数学`,
  5. if(element_at(kv, '英语') = null, null, kv['英语']) AS `英语`
  6. FROM (
  7. SELECT username, map_agg(subject, score) kv
  8. FROM pivot
  9. GROUP BY username
  10. ) t
  11. ORDER BY username;

结果:

行列转换方法2

列转行

方法一:通过UNION语句

  1. SELECT username, subject, score
  2. FROM (
  3. SELECT username, '语文' AS subject, yuwen AS score FROM unpivot WHERE yuwen is not null
  4. UNION
  5. SELECT username, '数学' AS subject, shuxue AS score FROM unpivot WHERE shuxue is not null
  6. UNION
  7. SELECT username, '英语' AS subject, yingyu AS score FROM unpivot WHERE yingyu is not null
  8. )
  9. ORDER BY username;

结果:

unpivot列转行结果图

方法二:通过CROSS JOIN UNNEST语句

  1. SELECT t1.username, t2.subject, t2.score
  2. FROM unpivot t1
  3. CROSS JOIN UNNEST (
  4. array['语文', '数学', '英语'],
  5. array[yuwen, shuxue, yingyu]
  6. ) t2 (subject, score)
  7. WHERE t2.score is not null

结果:

列转行方法2