MaxCompute:Best practices for transposing rows to columns or columns to rows

The following figure shows the implementation of transposing rows to columns and columns to rows.

• Rows to columns

  Transpose multiple rows to one row, or transpose one column to multiple columns.

• Columns to rows

  Transpose one row to multiple rows, or transpose multiple columns to one column.

You can use one of the following methods to transpose rows to columns:

• Method 1: Use the CASE WHEN expression to extract the values of each subject as separate columns. Sample statement:

  SELECT name AS name
         max(case subject when 'chinese' then result end) AS chinese,
         max(case subject when 'mathematics' then result end) AS mathematics,
         max(case subject when 'physics' then result end) AS physics 
  FROM rowtocolumn 
  GROUP BY name;

  The following result is returned:

  +--------+------------+------------+------------+
  | name   | chinese      | mathematics     | physics      |
  +--------+------------+------------+------------+
  | Bob       | 74               | 83                      | 93            |
  | Alice      | 74               | 84                      | 94        |
  +--------+------------+------------+------------+

• Method 2: Use built-in functions to transpose rows to columns. Merge the values of the subject and result columns into one column by using the CONCAT and WM_CONCAT functions. Then, parse the values of the subject column as separate columns by using the KEYVALUE function. Sample statement:

  SELECT name AS name,
         keyvalue(subject, chinese') AS chinese,
         keyvalue(subject, 'mathematics') AS mathematics,
         keyvalue(subject, 'physics') AS physics
  FROM(
       SELECT name, wm_concat(';',concat(subject,':',result))as subject 
       FROM rowtocolumn
       GROUP BY name);

  The following result is returned:

  +--------+------------+------------+------------+
  | name   | chinese      | mathematics     | physics      |
  +--------+------------+------------+------------+
  | Bob     | 74                 | 83                     | 93        |
  | Alice    | 74                 | 84                     | 94        |
  +--------+------------+------------+------------+

You can use one of the following methods to transpose columns to rows:

• Method 1: Use the UNION ALL clause to combine the values in chinese, mathematics, and physics columns into one column. Sample statements:

  -- Remove the limit on the simultaneous execution of the ORDER BY and LIMIT clauses. This way, you can use ORDER BY to sort the results by name. 
  SET odps.sql.validate.orderby.limit=false;
  -- Transpose columns to rows. 
  SELECT name AS name, subject AS subject, result AS result 
  FROM(
       SELECT name, 'chinese' AS subject, chinese AS result FROM columntorow 
       UNION all 
       SELECT name, 'mathematics' AS subject, mathematics AS result FROM columntorow 
       UNION all 
       SELECT name, 'physics' AS subject, physics AS result FROM columntorow) 
  ORDER BY name;

  The following result is returned:

  +--------+--------+------------+
  | name   | subject   | result       |
  +--------+--------+------------+
  | Bob   | chinese   | 74         |
  | Bob   | mathematics   | 83         |
  | Bob   | physics   | 93         |
  | Alice   | chinese   | 74         |
  | Alice   | mathematics   | 84         |
  | Alice   | physics   | 94         |
  +--------+--------+------------+

• Method 2: Use built-in functions to transpose columns to rows. Concatenate the column name of each subject and the values in each column by using the CONCAT function. Then, split the concatenated values into the subject and result columns as separate columns by using the TRANS_ARRAY and SPLIT_PART functions. Sample statement:

  Note

  The CONCAT function returns null if an input parameter is null. Therefore, if the table that you want to transpose contains a null value, Method 2 does not return the expected result. To resolve this issue in Method 2, you can use the NVL function to convert the null value into a special value, such as 0. For more information about the NVL function, see NVL. You can also use Method 1 instead to transpose columns to rows.

  SELECT name AS name,
         split_part(subject,':',1) AS subject,
         split_part(subject,':',2) AS result
  FROM(
         SELECT trans_array(1,';',name,subject) AS (name,subject) 
         FROM(
              SELECT name,
          concat('chinese',':',chinese,';','mathematics',':',mathematics,';','physics',':',physics) AS subject 
              FROM columntorow)tt)tx;

  The following result is returned:

  +--------+--------+------------+
  | name   | subject   | result       |
  +--------+--------+------------+
  | Bob   | chinese   | 74         |
  | Bob   | mathematics   | 83         |
  | Bob   | physics   | 93         |
  | Alice   | chinese   | 74         |
  | Alice   | mathematics   | 84         |
  | Alice   | physics   | 94         |
  +--------+--------+------------+