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:
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:
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: