Set rows value as column name using MySql version 8.0.17

Databases
1

Hi,

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

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

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

SELECT * FROM `t_contents_s3sv_1_2021` order by sID asc;
+-----------------------+--------+-----+
| 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             |
+-----------------------+-----------------------+

I tried to search posts, without any result either, maybe I didn’t use the right words.

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);
2

here ya go –

SELECT q400.row_number
     , q400.contents
     , q410.contents
  FROM ( SELECT contents
              , ROW_NUMBER() OVER()  AS rownumber
           FROM `t_contents_s3sv_1_2021` 
          WHERE sUnity = 'Q400'
         ORDER
             BY sID ) AS q400
LEFT OUTER
  JOIN ( SELECT contents
              , ROW_NUMBER() OVER()  AS rownumber
           FROM `t_contents_s3sv_1_2021` 
          WHERE sUnity = 'Q410'
         ORDER
             BY sID ) AS q410             
UNION  /* note UNION removes duplicates!! */
SELECT q410.row_number
     , q400.contents
     , q410.contents
  FROM ( SELECT contents
              , ROW_NUMBER() OVER()  AS rownumber
           FROM `t_contents_s3sv_1_2021` 
          WHERE sUnity = 'Q400'
         ORDER
             BY sID ) AS q400
RIGHT OUTER
  JOIN ( SELECT contents
              , ROW_NUMBER() OVER()  AS rownumber
           FROM `t_contents_s3sv_1_2021` 
          WHERE sUnity = 'Q410'
         ORDER
             BY sID ) AS q410

caution: untested

please answer this question here in this thread – SQL Query to union all table names on a database with MySQL - #7 by benanamen

because i, too, suspect you have a DB design problem

1 Like
3

thanks for help

your query (no tested) doesn’t work

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION

I am not the owner nor the designer of the DB

DB is in remote hosting

I am only user and don’t have privileges for fully use DB…

I have to adapt… on about work done by others that I cannot modify

4

yeah, maybe push down the left and right joins into subqueries

but you can see how nauseous the solutions are for what you want

instead, i urge you to run this query instead –

SELECT * FROM `t_contents_s3sv_1_2021` order by sID asc;

and then massage the results in whatever front-end language you’re using

1 Like
5

Yes!

Thanks!

6

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