Row to column transformation from different tables (union) in MySQL version 8.0.17 using Pivot

Question

I need union two different tables stored on MySql version 8.0.17 and on the return set rows values as column name using this link

First table

+-----------------------+--------+-----+
| contents              | sUnity | sID |
+-----------------------+--------+-----+
| Set n.1               | Q400   |  83 |
| - Par 1.1             | Q400   |  84 |
| <b>bold text</b>      | Q400   |  85 |
| - Par 1.2             | Q400   |  86 |
| normal text           | Q400   |  87 |
| Set n.2               | Q400   |  88 |
| - Par 2.1             | Q400   |  89 |
| <i>italic text</i>    | Q400   |  90 |
| - Par 2.2             | Q400   |  91 |
| <u>underline text</u> | Q400   |  92 |
| - Par 2.3             | Q400   |  93 |
+-----------------------+--------+-----+
11 rows in set (0.03 sec)

Second table

+-----------------------+--------+-----+
| contents              | sUnity | sID |
+-----------------------+--------+-----+
| Set n.1               | Q410   |  94 |
| - Par 1.1             | Q410   |  95 |
| <b>bold text</b>      | Q410   |  96 |
| - Par 1.2             | Q410   |  97 |
| normal text           | Q410   |  98 |
| Set n.2               | Q410   |  99 |
| - Par 2.1             | Q410   | 100 |
| <i>italic text</i>    | Q410   | 101 |
| - Par 2.2             | Q410   | 102 |
| <u>underline text</u> | Q410   | 103 |
| - Par 2.3             | Q410   | 104 |
+-----------------------+--------+-----+
11 rows in set (0.02 sec)

For this return

+-----------------------+-----------------------+
| Q400                  | Q410                  |
+-----------------------+-----------------------+
| Set n.1               | Set n.1               |
| - Par 1.1             | - Par 1.1             |
| <b>bold text</b>      | <b>bold text</b>      |
| - Par 1.2             | - Par 1.2             |
| normal text           | normal text           |
| Set n.2               | Set n.2               |
| - Par 2.1             | - Par 2.1             |
| <i>italic text</i>    | <i>italic text</i>    |
| - Par 2.2             | - Par 2.2             |
| <u>underline text</u> | <u>underline text</u> |
| - Par 2.3             | - Par 2.3             |
+-----------------------+-----------------------+

Unfortunately MySQL does not have a PIVOT function which is basically for what I trying to do.

I have tried this query and I have the return below

SET @row_number := 0;
SELECT
    MAX(
    IF
    ( sUnity = 'Q400', contents, NULL )) Q400 
FROM
    (
    SELECT
        @row_number :=
    CASE            
            WHEN @sUnity = sUnity THEN
            @row_number + 1 ELSE 1 
        END AS num,
        @sUnity := sUnity sUnity,
        contents 
    FROM
        t_contents_Q400 
    ORDER BY
        sUnity,
        sID 
    ) t 
GROUP BY
    num UNION ALL
SELECT
    MAX(
    IF
    ( sUnity = 'Q410', contents, NULL )) Q410 
FROM
    (
    SELECT
        @row_number :=
    CASE
            
            WHEN @sUnity = sUnity THEN
            @row_number + 1 ELSE 1 
        END AS num,
        @sUnity := sUnity sUnity,
        contents 
    FROM
        t_contents_Q410 
    ORDER BY
        sUnity,
        sID 
    ) t 
GROUP BY
    num;

+-----------------------+
| Q400                  |
+-----------------------+
| Set n.2               |
| Set n.1               |
| - Par 1.1             |
| <b>bold text</b>      |
| - Par 1.2             |
| normal text           |
| Set n.2               |
| - Par 2.1             |
| <i>italic text</i>    |
| - Par 2.2             |
| <u>underline text</u> |
| - Par 2.3             |
+-----------------------+
12 rows in set (0.04 sec)

My structures and data tables below

-- ----------------------------
-- Table structure for t_contents_q400
-- ----------------------------
DROP TABLE IF EXISTS `t_contents_q400`;
CREATE TABLE `t_contents_q400`  (
  `contents` varchar(255) DEFAULT NULL,
  `sUnity` varchar(50) DEFAULT NULL,
  `sID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`sID`) USING BTREE,
  UNIQUE INDEX `contents`(`contents`, `sUnity`) USING BTREE
) ENGINE = InnoDB;

-- ----------------------------
-- Records of t_contents_q400
-- ----------------------------
INSERT INTO `t_contents_q400` VALUES ('- Par 1.1', 'Q400', 1);
INSERT INTO `t_contents_q400` VALUES ('- Par 1.2', 'Q400', 2);
INSERT INTO `t_contents_q400` VALUES ('- Par 2.1', 'Q400', 3);
INSERT INTO `t_contents_q400` VALUES ('- Par 2.2', 'Q400', 4);
INSERT INTO `t_contents_q400` VALUES ('- Par 2.3', 'Q400', 5);
INSERT INTO `t_contents_q400` VALUES ('<b>bold text</b>', 'Q400', 6);
INSERT INTO `t_contents_q400` VALUES ('<i>italic text</i>', 'Q400', 7);
INSERT INTO `t_contents_q400` VALUES ('<u>underline text</u>', 'Q400', 8);
INSERT INTO `t_contents_q400` VALUES ('normal text', 'Q400', 9);
INSERT INTO `t_contents_q400` VALUES ('Set n.1', 'Q400', 10);
INSERT INTO `t_contents_q400` VALUES ('Set n.2', 'Q400', 11);

-- ----------------------------
-- Table structure for t_contents_q410
-- ----------------------------
DROP TABLE IF EXISTS `t_contents_q410`;
CREATE TABLE `t_contents_q410`  (
  `contents` varchar(255) DEFAULT NULL,
  `sUnity` varchar(50) DEFAULT NULL,
  `sID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`sID`) USING BTREE,
  UNIQUE INDEX `contents`(`contents`, `sUnity`) USING BTREE
) ENGINE = InnoDB;

-- ----------------------------
-- Records of t_contents_q410
-- ----------------------------
INSERT INTO `t_contents_q410` VALUES ('- Par 1.1', 'Q410', 1);
INSERT INTO `t_contents_q410` VALUES ('- Par 1.2', 'Q410', 2);
INSERT INTO `t_contents_q410` VALUES ('- Par 2.1', 'Q410', 3);
INSERT INTO `t_contents_q410` VALUES ('- Par 2.2', 'Q410', 4);
INSERT INTO `t_contents_q410` VALUES ('- Par 2.3', 'Q410', 5);
INSERT INTO `t_contents_q410` VALUES ('<b>bold text</b>', 'Q410', 6);
INSERT INTO `t_contents_q410` VALUES ('<i>italic text</i>', 'Q410', 7);
INSERT INTO `t_contents_q410` VALUES ('<u>underline text</u>', 'Q410', 8);
INSERT INTO `t_contents_q410` VALUES ('normal text', 'Q410', 9);
INSERT INTO `t_contents_q410` VALUES ('Set n.1', 'Q410', 10);
INSERT INTO `t_contents_q410` VALUES ('Set n.2', 'Q410', 11);

Are you sure the two tables always have the same number of rows? — Gordon Linoff, Mar 06 '21 at 18:26
. . The answer that you accepted assumes that they do -- or returns the number of rows in the smaller table. I'm not sure if that is what you intend. — Gordon Linoff, Mar 07 '21 at 17:15
@GordonLinoff I intend union all tables independently by the rows numbers... it may be that the number of rows between tables to be joined is different, but all rows must be presentin in the return — Edward Sheriff Curtis, Mar 07 '21 at 17:30
Then you should check that the any you accepted really does what you want. I don't think it does. — Gordon Linoff, Mar 07 '21 at 20:10
@GordonLinoff Okay, pls see https://stackoverflow.com/questions/66528183/row-to-column-transformation-from-different-tables-and-different-rows-number-un — Edward Sheriff Curtis, Mar 08 '21 at 10:32

score 1 · Accepted Answer · answered Mar 06 '21 at 17:29

In MySQL 8.0+ you can do it with ROW_NUMBER() window function in each table to get a row number and join the tables on that:

WITH 
  cte1 AS (SELECT *, ROW_NUMBER() OVER (ORDER BY sID) rn FROM t_contents_q400),
  cte2 AS (SELECT *, ROW_NUMBER() OVER (ORDER BY sID) rn FROM t_contents_q410)
SELECT c1.contents Q400, c2.contents Q410
FROM cte1 c1 INNER JOIN cte2 c2
ON c2.rn = c1.rn

I assume that the 2 tables have the same number of rows, like your sample data.

See the demo.

Row to column transformation from different tables (union) in MySQL version 8.0.17 using Pivot

1 Answers1

Linked