All Products
Search
Document Center

Data Lake Analytics - Deprecated:Convert between rows and columns

Last Updated:May 10, 2019

Convert rows to columns

Method 1: Use the CASE WHEN clause

  1. SELECT
  2. username,
  3. max(CASE WHEN subject = 'Chinese' THEN score END) AS `Chinese`,
  4. max(CASE WHEN subject = 'Math' THEN score END) AS `Math`,
  5. max(CASE WHEN subject = 'English' THEN score END) AS `English`
  6. FROM pivot
  7. GROUP BY username
  8. ORDER BY username;

Result:

Method 1 for converting rows to columns

Method 2: Use the map_agg function

Follow these steps to convert rows to columns by using the map_agg function:

  1. Use the map_agg function to map the values of multiple rows in two columns to a map.

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

    Result:

    Result of the map_agg function

  2. Use the map to create multiple output columns.

The final SQL statement that uses the map_agg function to convert rows to columns is as follows:

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

Result:

Method 2 for converting rows to columns

Convert columns to rows

Method 1: Use the UNION operator

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

Method 2: Use the CROSS JOIN UNNEST function

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

Result:

Method 2 for converting columns to rows