[MySQL] Making vertical data horizontal (vertical-horizontal)

---

---

Making vertical data horizontal (vertical-horizontal)

Explains how to make horizontal data vertically. First, compare the data before and after.

Data before processing

+-----+------+-------+-------+  
| idx | name | class | score |  
+-----+------+-------+-------+  
|   7 | choi | kor   |    90 |  
|   8 | choi | eng   |    80 |  
|   9 | choi | math  |    70 |  
|  10 | kim  | kor   |    60 |  
|  11 | kim  | eng   |    85 |  
|  12 | kim  | math  |   100 |  
+-----+------+-------+-------+  

Data after processing

+------+------+------+------+  
| name | kor  | eng  | math |  
+------+------+------+------+  
| choi |   90 |   80 |   70 |  
| kim  |   60 |   85 |  100 |  
+------+------+------+------+  

If you look at the data after processing, you can see that kor, eng, and math items are arranged horizontally by name.

Species data sample generation

Let's create a hypothetical sample data to use as species data.

CREATE TABLE `score` (  
`idx` int(11) NOT NULL AUTO_INCREMENT,  
`name` varchar(32) NOT NULL,  
`class` varchar(32) NOT NULL,  
`score` int(11) NOT NULL,  
PRIMARY KEY (`idx`)  
);  
  
INSERT INTO score (name, class, score) VALUES ('choi', 'kor', 90);  
INSERT INTO score (name, class, score) VALUES ('choi', 'eng', 80);  
INSERT INTO score (name, class, score) VALUES ('choi', 'math', 70);  
  
INSERT INTO score (name, class, score) VALUES ('kim', 'kor', 60);  
INSERT INTO score (name, class, score) VALUES ('kim', 'eng', 85);  
INSERT INTO score (name, class, score) VALUES ('kim', 'math', 100);  

Species data generation is complete.

mysql> SELECT * FROM score;  
+-----+------+-------+-------+  
| idx | name | class | score |  
+-----+------+-------+-------+  
|   7 | choi | kor   |    90 |  
|   8 | choi | eng   |    80 |  
|   9 | choi | math  |    70 |  
|  10 | kim  | kor   |    60 |  
|  11 | kim  | eng   |    85 |  
|  12 | kim  | math  |   100 |  
+-----+------+-------+-------+  

Creating a column using alias

To make vertical data into lateral data consisting of kor, eng, and math, K, E, M columns are created using aliases, and kor data is K, eng data is E, and math data is M.

For columns that do not contain values, NULL is stored.

SELECT  
name,  
CASE WHEN class = 'kor' THEN score END AS K,  
CASE WHEN class = 'eng' THEN score END AS E,  
CASE WHEN class = 'math' THEN score END AS M  
FROM score;  
  
+------+------+------+------+  
| name | K    | E    | M    |  
+------+------+------+------+  
| choi |   90 | NULL | NULL |  
| choi | NULL |   80 | NULL |  
| choi | NULL | NULL |   70 |  
| kim  |   60 | NULL | NULL |  
| kim  | NULL |   85 | NULL |  
| kim  | NULL | NULL |  100 |  
+------+------+------+------+  

It is still in the form of vertical data, but it is now ready to be made into horizontal data.

Generate transverse data

Create an arbitrary table using the query created above.
Group by an arbitrary table and create horizontal data using K, E, M columns created using alias.
K, E, M columns are added using the SUM function, and the column names are designated with kor, eng, and math using alias.

SELECT name, SUM(K) as kor, SUM(E) as eng, SUM(M) as math FROM  
(  
SELECT  
name,  
CASE WHEN class = 'kor' THEN score END AS K,  
CASE WHEN class = 'eng' THEN score END AS E,  
CASE WHEN class = 'math' THEN score END AS M  
FROM score  
)AS T GROUP BY name;  
  
+------+------+------+------+  
| name | kor  | eng  | math |  
+------+------+------+------+  
| choi |   90 |   80 |   70 |  
| kim  |   60 |   85 |  100 |  
+------+------+------+------+  

Reference URL

http://stackoverflow.com/questions/1241178/mysql-rows-to-columns