MySQL Pivot rotating rows to columns

Databases
1

This is my version of MySQL database

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.17    |
+-----------+
1 row in set

I have created to a MySQL database and two related tables where rows of one table will be converted into the columns like PIVOT() function

table t_name

mysql> SELECT sName FROM `t_name`;
+-------+
| sName |
+-------+
| 1D    |
| 1E    |
| 1L    |
| 2A    |
| 2C    |
| 2F    |
| 2H    |
| 2P    |
| 3B    |
| 3E    |
| 3H    |
| 4B    |
| 4D    |
| 4G    |
| 5H    |
+-------+
15 rows in set

table t_chapter

mysql> SELECT * FROM `t_chapter`;
+----------+--------+-----+
| sCHAPTER | sTITLE | sID |
+----------+--------+-----+
|        1 | ES     |   1 |
|        2 | SA     |   2 |
|        3 | ECO    |   3 |
|        4 | PER    |   4 |
|        5 | ESEM   |   5 |
|        6 | CMR    |   6 |
|        7 | SVRE   |   7 |
|        8 | AVA    |   8 |
|        9 | INT    |   9 |
|       10 | SPM    |  10 |
+----------+--------+-----+
10 rows in set

PIVOT() function on t_chapter

mysql> SELECT 
CASE WHEN sCHAPTER = "1" THEN NULL END "ES",
CASE WHEN sCHAPTER = "2" THEN NULL END "SA",
CASE WHEN sCHAPTER = "3" THEN NULL END "ECO",
CASE WHEN sCHAPTER = "4" THEN NULL END "PER",
CASE WHEN sCHAPTER = "5" THEN NULL END "ESEM",
CASE WHEN sCHAPTER = "6" THEN NULL END "CMR",
CASE WHEN sCHAPTER = "7" THEN NULL END "SVRE",
CASE WHEN sCHAPTER = "8" THEN NULL END "AVA",
CASE WHEN sCHAPTER = "9" THEN NULL END "INT",
CASE WHEN sCHAPTER = "10" THEN NULL END "SMP"
FROM `t_chapter`;

+------+------+------+------+------+------+------+------+------+------+
| ES   | SA   | ECO  | PER  | ESEM | CMR  | SVRE | AVA  | INT  | SMP  |
+------+------+------+------+------+------+------+------+------+------+
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+------+------+------+------+------+------+------+------+------+------+
10 rows in set

Now I need union on t_chapter the values of t_name for this return

+-------+------+------+------+------+------+------+------+------+------+------+
| sName | ES   | SA   | ECO  | PER  | ESEM | CMR  | SVRE | AVA  | INT  | SMP  |
+-------+------+------+------+------+------+------+------+------+------+------+
| 1D    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 1E    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 1L    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2A    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2C    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2F    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2H    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2P    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 3B    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 3E    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 3H    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 4B    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 4D    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 4G    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 5H    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+-------+------+------+------+------+------+------+------+------+------+------+

How to do resolve this?

Please, any suggestion?

My tables below

-- ----------------------------
-- Table structure for t_chapter
-- ----------------------------
DROP TABLE IF EXISTS `t_chapter`;
CREATE TABLE `t_chapter` (
  `sCHAPTER` int(11) DEFAULT NULL,
  `sTITLE` char(150) DEFAULT NULL,
  `sID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`sID`),
  UNIQUE KEY `sCHAPTER` (`sCHAPTER`)
) ENGINE=MyISAM;

-- ----------------------------
-- Records of t_chapter
-- ----------------------------
INSERT INTO `t_chapter` VALUES ('1', 'ES', '1');
INSERT INTO `t_chapter` VALUES ('2', 'SA', '2');
INSERT INTO `t_chapter` VALUES ('3', 'ECO', '3');
INSERT INTO `t_chapter` VALUES ('4', 'PER', '4');
INSERT INTO `t_chapter` VALUES ('5', 'ESEM', '5');
INSERT INTO `t_chapter` VALUES ('6', 'CMR', '6');
INSERT INTO `t_chapter` VALUES ('7', 'SVRE', '7');
INSERT INTO `t_chapter` VALUES ('8', 'AVA', '8');
INSERT INTO `t_chapter` VALUES ('9', 'INT', '9');
INSERT INTO `t_chapter` VALUES ('10', 'SPM', '10');

-- ----------------------------
-- Table structure for t_name
-- ----------------------------
DROP TABLE IF EXISTS `t_name`;
CREATE TABLE `t_name` (
  `sName` char(10) DEFAULT NULL,
  `sID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`sID`),
  UNIQUE KEY `sName` (`sName`)
) ENGINE=MyISAM;
2

Unions add rows. JOINs add columns. (In general.)

Also that’s not a pivot function, that’s you mimicing a pivot function with a bunch of case statements :wink:

What DATA is meant to be in your pivot?

3

Thanks for reply

this DATA


+-------+------+------+------+------+------+------+------+------+------+------+
| sName | ES   | SA   | ECO  | PER  | ESEM | CMR  | SVRE | AVA  | INT  | SMP  |
+-------+------+------+------+------+------+------+------+------+------+------+
| 1D    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 1E    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 1L    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2A    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2C    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2F    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2H    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2P    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 3B    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 3E    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 3H    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 4B    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 4D    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 4G    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 5H    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+-------+------+------+------+------+------+------+------+------+------+------+
4 
SELECT t_name.sname
     , pivot.*
  FROM t_name
CROSS
  JOIN ( SELECT MAX(CASE WHEN sCHAPTER = '1' THEN NULL END ) ES
              , MAX(CASE WHEN sCHAPTER = '2' THEN NULL END ) SA
              , MAX(CASE WHEN sCHAPTER = '3' THEN NULL END ) ECO
              , MAX(CASE WHEN sCHAPTER = '4' THEN NULL END ) PER
              , MAX(CASE WHEN sCHAPTER = '5' THEN NULL END ) ESEM
              , MAX(CASE WHEN sCHAPTER = '6' THEN NULL END ) CMR
              , MAX(CASE WHEN sCHAPTER = '7' THEN NULL END ) SVRE
              , MAX(CASE WHEN sCHAPTER = '8' THEN NULL END ) AVA
              , MAX(CASE WHEN sCHAPTER = '9' THEN NULL END ) INT
              , MAX(CASE WHEN sCHAPTER = '10' THEN NULL END) SMP
           FROM t_chapter
       ) pivot
1 Like
5

I guess i’m just having trouble understanding why you want a pivot full of NULL’s…
Since MySQL doesnt have a PIVOT command, until someone comes along and shows me how to do it, to generate your output:

SELECT `t_name`.`sName`,       
	   NULL "ES",
       NULL "SA",
       NULL "ECO",
       NULL "PER",
       NULL "ESEM",
       NULL "CMR",
       NULL "SVRE",
       NULL "AVA",
       NULL "INT",
       NULL "SMP"
FROM   `t_name`

(Just… dont see the point in the t_chapter table, if you’re not going to reference data out of it?)

1 Like
6

Careful with that column name, tho. INT is reserved.

1 Like
7

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.