Search
Document Center

# Convert between rows and columns

Last Updated: Aug 05, 2019

## Convert rows to columns

### Method 1: Use the CASE WHEN clause

``SELECT ``   username,``   max(CASE WHEN subject = 'Chinese' THEN score END) AS `Chinese`,``   max(CASE WHEN subject = 'Math' THEN score END) AS `Math`,``   max(CASE WHEN subject = 'English' THEN score END) AS `English```FROM pivot``GROUP BY username``ORDER BY username;``

Result:

### 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.

``SELECT username, map_agg(subject, score) kv`` FROM pivot`` GROUP BY username`` ORDER BY username;``

Result:

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:

``SELECT``  username,``  if(element_at(kv, 'Chinese') = null, null, kv['Chinese']) AS `Chinese`,``  if(element_at(kv, 'Math') = null, null, kv['Math']) AS `Math`,``  if(element_at(kv, 'English') = null, null, kv['English']) AS `English```FROM (``  SELECT username, map_agg(subject, score) kv``  FROM pivot``  GROUP BY username``) t``ORDER BY username;``

Result:

## Convert columns to rows

### Method 1: Use the UNION operator

``SELECT username, subject, score``FROM (``  SELECT username, 'Chinese' AS subject, yuwen AS score FROM unpivot WHERE yuwen is not null``  UNION``  SELECT username, 'Math' AS subject, shuxue AS score FROM unpivot WHERE shuxue is not null``  UNION``  SELECT username, 'English' AS subject, yingyu AS score FROM unpivot WHERE yingyu is not null``)``ORDER BY username;``

### Method 2: Use the CROSS JOIN UNNEST function

``SELECT t1.username, t2.subject, t2.score``FROM unpivot t1``CROSS JOIN UNNEST (``  array['Chinese', 'Math', 'English'],``  array[yuwen, shuxue, yingyu]``) t2 (subject, score)``WHERE t2.score is not null``

Result: