locked
Row to column transformation in MySQL version 8.0.17 using Pivot RRS feed

  • Question

  • User-1453200658 posted

    Hi

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

    I populate a table of a database MySql version 8.0.17, with an external file in csv format.

    This is the table filled with data from the external csv file

    +-----------------------+--------+-----+
    | contents              | sUnity | sID |
    +-----------------------+--------+-----+
    | Set n.1               | Q400   |   4 |
    | - Par 1.1             | Q400   |   6 |
    | <b>bold text</b>      | Q400   |   7 |
    | - Par 1.2             | Q400   |   9 |
    | normal text           | Q400   |  10 |
    | Set n.2               | Q400   |  12 |
    | - Par 2.1             | Q400   |  14 |
    | <i>italic text</i>    | Q400   |  15 |
    | - Par 2.2             | Q400   |  16 |
    | <u>underline text</u> | Q400   |  17 |
    | - Par 2.3             | Q400   |  71 |
    | Set n.1               | Q410   |  72 |
    | - Par 1.1             | Q410   |  73 |
    | <b>bold text</b>      | Q410   |  74 |
    | - Par 1.2             | Q410   |  75 |
    | normal text           | Q410   |  76 |
    | Set n.2               | Q410   |  77 |
    | - Par 2.1             | Q410   |  78 |
    | <i>italic text</i>    | Q410   |  79 |
    | - Par 2.2             | Q410   |  80 |
    | <u>underline text</u> | Q410   |  81 |
    | - Par 2.3             | Q410   |  82 |
    +-----------------------+--------+-----+
    22 rows in set (0.03 sec)
    

    Now I need this return I mean set rows values as column name

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

    Stored procedure and return ( I'm sorry but if I adding the SP code this question is not released... )

    enter image description here

    +-----+-------------+-------------+--------+
    | sID | sUnity_Q400 | sUnity_Q410 | sUnity |
    +-----+-------------+-------------+--------+
    |   6 | Q400        | NULL        | Q400   |
    |  73 | NULL        | Q410        | Q410   |
    |   9 | Q400        | NULL        | Q400   |
    |  75 | NULL        | Q410        | Q410   |
    |  14 | Q400        | NULL        | Q400   |
    |  78 | NULL        | Q410        | Q410   |
    |  16 | Q400        | NULL        | Q400   |
    |  80 | NULL        | Q410        | Q410   |
    |  71 | Q400        | NULL        | Q400   |
    |  82 | NULL        | Q410        | Q410   |
    |   7 | Q400        | NULL        | Q400   |
    |  74 | NULL        | Q410        | Q410   |
    |  15 | Q400        | NULL        | Q400   |
    |  79 | NULL        | Q410        | Q410   |
    |  17 | Q400        | NULL        | Q400   |
    |  81 | NULL        | Q410        | Q410   |
    |  10 | Q400        | NULL        | Q400   |
    |  76 | NULL        | Q410        | Q410   |
    |   4 | Q400        | NULL        | Q400   |
    |  72 | NULL        | Q410        | Q410   |
    |  12 | Q400        | NULL        | Q400   |
    |  77 | NULL        | Q410        | Q410   |
    +-----+-------------+-------------+--------+
    22 rows in set (0.05 sec)
    

    Structure and data of table below

    -- ----------------------------
    -- Table structure for t_contents_s3sv_1_2021
    -- ----------------------------
    DROP TABLE IF EXISTS `t_contents_s3sv_1_2021`;
    CREATE TABLE `t_contents_s3sv_1_2021`  (
      `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_s3sv_1_2021
    -- ----------------------------
    INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 1.1', 'Q400', 6);
    INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 1.1', 'Q410', 73);
    INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 1.2', 'Q400', 9);
    INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 1.2', 'Q410', 75);
    INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 2.1', 'Q400', 14);
    INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 2.1', 'Q410', 78);
    INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 2.2', 'Q400', 16);
    INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 2.2', 'Q410', 80);
    INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 2.3', 'Q400', 71);
    INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 2.3', 'Q410', 82);
    INSERT INTO `t_contents_s3sv_1_2021` VALUES ('<b>bold text</b>', 'Q400', 7);
    INSERT INTO `t_contents_s3sv_1_2021` VALUES ('<b>bold text</b>', 'Q410', 74);
    INSERT INTO `t_contents_s3sv_1_2021` VALUES ('<i>italic text</i>', 'Q400', 15);
    INSERT INTO `t_contents_s3sv_1_2021` VALUES ('<i>italic text</i>', 'Q410', 79);
    INSERT INTO `t_contents_s3sv_1_2021` VALUES ('<u>underline text</u>', 'Q400', 17);
    INSERT INTO `t_contents_s3sv_1_2021` VALUES ('<u>underline text</u>', 'Q410', 81);
    INSERT INTO `t_contents_s3sv_1_2021` VALUES ('normal text', 'Q400', 10);
    INSERT INTO `t_contents_s3sv_1_2021` VALUES ('normal text', 'Q410', 76);
    INSERT INTO `t_contents_s3sv_1_2021` VALUES ('Set n.1', 'Q400', 4);
    INSERT INTO `t_contents_s3sv_1_2021` VALUES ('Set n.1', 'Q410', 72);
    INSERT INTO `t_contents_s3sv_1_2021` VALUES ('Set n.2', 'Q400', 12);
    INSERT INTO `t_contents_s3sv_1_2021` VALUES ('Set n.2', 'Q410', 77);
    Friday, March 5, 2021 10:43 AM

Answers

  • User269602965 posted

    Yes Oracle has PIVOT, UNPIVOT and so much more.  Developer can use it for free.  I use it.

    Back to MySQL.

    The workaround pivot to get the table you showed.

    SELECT
       IF(sUnity = 'Q400',contents,NULL) AS Q400,
       IF(sUnity = 'Q410',contents,NULL) AS Q410
     FROM
       t_contents_s3sv_1_2021 
     ORDER BY
       sID
     /
     
     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, March 7, 2021 6:37 PM