SQL tutorial — row column transform

In this case, I intend to introduce a complicated SQL query problem: row column transform. In the last chapter, about the fundamental SQL syntax, I used a MySQL sample database named sakila. In this tutorial, I still use it.

Transform rows to columns

Task #1: Take table actor inside sakila for example, How to query the most popular last_names? let limit the condition to the last_names repeat at least more than 3 times.

USE sakila;
SELECT last_name, COUNT(first_NAME) as size FROM
actor GROUP BY last_name having size > 3;

We got the result:

+-----------+------+
| last_name | size |
+-----------+------+
| KILMER | 5 |
| NOLTE | 4 |
| TEMPLE | 4 |
+-----------+------+

Task #2: Convert above result from row to column format.

+--------+-------+--------+
| KILMER | NOLTE | TEMPLE |
+--------+-------+--------+
| 5 | 4 | 4 |
+--------+-------+--------+

Use CASE WHEN and combine with AGGREGATION FUNCTION to archive the target.

SELECT SUM(case when m.last_name='KILMER' then m.size end) as 'KILMER',
SUM(case when m.last_name='NOLTE' then m.size end) as 'NOLTE',
SUM(case when m.last_name='TEMPLE' then m.size end) as 'TEMPLE'
FROM (SELECT last_name, COUNT(first_NAME) as size
FROM actor
GROUP BY last_name HAVING size > 3) m;

Task #3: Write above results to a new table

CREATE TABLE t_column_test (
KILMER smallint(5) unsigned,
NOLTE smallint(5) unsigned,
TEMPLE smallint(5) unsigned
)
SELECT SUM(case when m.last_name='KILMER' then m.size end) as 'KILMER',
SUM(case when m.last_name='NOLTE' then m.size end) as 'NOLTE',
SUM(case when m.last_name='TEMPLE' then m.size end) as 'TEMPLE'
FROM (SELECT last_name, COUNT(first_NAME) as size
FROM actor
GROUP BY last_name HAVING size > 3) m;

Then the newly create table t_column_test just have one row record.

Transform columns to rows

Task #4: transform above columns result back to row.

SELECT 'KILMER' as last_name, KILMER as size FROM t_column_test
UNION ALL
SELECT 'NOLTE' as last_name, NOLTE as size FROM t_column_test
UNION ALL
SELECT 'TEMPLE' as last_name, TEMPLE as size FROM t_column_test;

The result converts back.

+-----------+------+
| last_name | size |
+-----------+------+
| KILMER | 5 |
| NOLTE | 4 |
| TEMPLE | 4 |
+-----------+------+