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