CODEWITHSUNDEEP
mysqlpivot-tablemysql-error-1064
Edward Sheriff Curtis
Edward Sheriff Curtis

Reputation: 497

Using pivot to join tables using Mysql 8 version

I've these two tables t1 and t2 stored an a database MySql version 8.0.17

It's important to say that the table t2 can contain thousands of rows with different unit code (field sUn)...

Instead the table t1 contains only the how and code of each unit (field TABLE_NAME)

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

-- ----------------------------
-- Records of t1
-- ----------------------------
INSERT INTO `t1` VALUES ('100', '2021-04-09 12:44:30', 1);
INSERT INTO `t1` VALUES ('11c', '2021-04-09 12:44:30', 2);
INSERT INTO `t1` VALUES ('11f', '2021-04-09 12:44:30', 3);
INSERT INTO `t1` VALUES ('12a', '2021-04-09 12:44:30', 4);
INSERT INTO `t1` VALUES ('12h', '2021-04-09 12:44:30', 5);

-- ----------------------------
-- Table structure for t2
-- ----------------------------
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2`  (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sUn` varchar(255) DEFAULT NULL,
  `sUnName` varchar(255) DEFAULT NULL,
  `sContents` longtext NULL,
  PRIMARY KEY (`sid`) USING BTREE
) ENGINE = InnoDB;

-- ----------------------------
-- Records of t2
-- ----------------------------
INSERT INTO `t2` VALUES (1, '100', 'NOR', 'Ipsa sua melior fama.');
INSERT INTO `t2` VALUES (2, '100', 'NOR', 'In toto.');
INSERT INTO `t2` VALUES (3, '100', 'NOR', 'Homines, nihil agendo.');
INSERT INTO `t2` VALUES (4, '11C', 'SAR', 'Habere non haberi.');
INSERT INTO `t2` VALUES (5, '11C', 'SAR', 'Vivere est cogitare.');
INSERT INTO `t2` VALUES (6, '11C', 'SAR', 'Urbi et Orbi.');
INSERT INTO `t2` VALUES (7, '11F', 'SAD', 'Inter sidera versor.');
INSERT INTO `t2` VALUES (8, '11F', 'SAD', 'Una tantum.');
INSERT INTO `t2` VALUES (9, '11F', 'SAD', 'Carthago delenda est.');
INSERT INTO `t2` VALUES (10, '12A', 'RIV', 'Status quo.');
INSERT INTO `t2` VALUES (11, '12A', 'RIV', 'Aut aut.');
INSERT INTO `t2` VALUES (12, '12A', 'RIV', 'Condicio sine qua non.\r\n');
INSERT INTO `t2` VALUES (13, '12H', 'CUN', 'Ubi maior minor cessat.');
INSERT INTO `t2` VALUES (14, '12H', 'CUN', 'Carpe diem.');
INSERT INTO `t2` VALUES (15, '12H', 'CUN', 'Venni, vidi, vinsi.');

I've tried -without success- one query pivot join these two tables for this return

+------------------------+----------------------+-----------------------+------------------------+-------------------------+
| NOR                    | SAR                  | SAD                   | RIV                    | CUN                     |
+------------------------+----------------------+-----------------------+------------------------+-------------------------+
| Ipsa sua melior fama.  | Habere non haberi.   | Inter sidera versor.  | Status quo.            | Ubi maior minor cessat. |
| In toto.               | Vivere est cogitare. | Una tantum.           | Aut aut.               | Carpe diem.             |
| Homines, nihil agendo. | Urbi et Orbi.        | Carthago delenda est. | Condicio sine qua non. | Venni, vidi, vinsi.     |
+------------------------+----------------------+-----------------------+------------------------+-------------------------+

My query below with error

Any help would greatly appreciate... Thank you.

SELECT
    max(
    IF
    ( t.sUn = q.TABLE_NAME, t.sContents, NULL )) q.sUnName
FROM
    ( SELECT row_number() over ( PARTITION BY sUn ORDER BY sID ) AS rn, tcs.sUn, tcs.sContents FROM t2 AS tcs ) AS t    
    JOIN t1 q ON t.sUn = q.TABLE_NAME   
GROUP BY
    rn 
ORDER BY
    rn

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 '.sUnName FROM ( SELECT row_number() over ( PARTITION BY sUn ORDER BY sID ) AS' at line 4 > Time: 0,001s

UPDATE

New version using stored procedure...

CREATE DEFINER=`root`@`localhost` PROCEDURE `pivot_20210409`()
BEGIN

SET SESSION group_concat_max_len = 1000000;

SELECT
  CONCAT('SELECT ',
    GROUP_CONCAT(
      CONCAT (
        "IF(`sUnName` = '",sUnName,'\',sContents,NULL) AS `',sUnName,'`'
      )
    ),
    ' FROM `t2`'
  ) INTO @SQL
FROM
 ( SELECT row_number() over ( PARTITION BY sUn ORDER BY sID ) AS rn, tcs.sUnName, tcs.sContents FROM t2 AS tcs
 GROUP BY tcs.sUnName
) AS data;

SELECT @SQL;

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END

return

+------------------------+----------------------+-----------------------+--------------------------+-------------------------+
| NOR                    | SAR                  | SAD                   | RIV                      | CUN                     |
+------------------------+----------------------+-----------------------+--------------------------+-------------------------+
| Ipsa sua melior fama.  | NULL                 | NULL                  | NULL                     | NULL                    |
| In toto.               | NULL                 | NULL                  | NULL                     | NULL                    |
| Homines, nihil agendo. | NULL                 | NULL                  | NULL                     | NULL                    |
| NULL                   | Habere non haberi.   | NULL                  | NULL                     | NULL                    |
| NULL                   | Vivere est cogitare. | NULL                  | NULL                     | NULL                    |
| NULL                   | Urbi et Orbi.        | NULL                  | NULL                     | NULL                    |
| NULL                   | NULL                 | Inter sidera versor.  | NULL                     | NULL                    |
| NULL                   | NULL                 | Una tantum.           | NULL                     | NULL                    |
| NULL                   | NULL                 | Carthago delenda est. | NULL                     | NULL                    |
| NULL                   | NULL                 | NULL                  | Status quo.              | NULL                    |
| NULL                   | NULL                 | NULL                  | Aut aut.                 | NULL                    |
| NULL                   | NULL                 | NULL                  | Condicio sine qua non.   | NULL                    |
| NULL                   | NULL                 | NULL                  | NULL                     | Ubi maior minor cessat. |
| NULL                   | NULL                 | NULL                  | NULL                     | Carpe diem.             |
| NULL                   | NULL                 | NULL                  | NULL                     | Venni, vidi, vinsi.     |
+------------------------+----------------------+-----------------------+--------------------------+-------------------------+

Upvotes: 0

Views: 1378

Answers (1)

O. Jones
O. Jones

Reputation: 108806

In MySQL, sad to say, pivoting is hard to do for a mess of arbitrarily named columns.

This gets the result you want (dbfiddle on db-fiddle.com, which offers MySQL 8.)

WITH pivot AS (
  SELECT CASE WHEN SunName = 'NOR' THEN sContents ELSE NULL END NOR,
         CASE WHEN SunName = 'SAR' THEN sContents ELSE NULL END SAR,
         CASE WHEN SunName = 'SAD' THEN sContents ELSE NULL END SAD,
         CASE WHEN SunName = 'RIV' THEN sContents ELSE NULL END RIV,
         CASE WHEN SunName = 'CUN' THEN sContents ELSE NULL END CUN,
         sId
   FROM t2
), 
rownums AS (
  SELECT sId, row_number() OVER ( PARTITION BY sUn ORDER BY sID ) rn FROM t2
)
SELECT MAX(NOR) NOR, MAX(SAR) SAR, MAX(SAD) SAD, MAX(RIV) RIV, MAX(CUN) CUN
  FROM rownums
  JOIN pivot ON rownums.sId = pivot.sId
 GROUP BY rownums.rn

This nasty subquery does the pivoting operation. (fiddle) See how it enumerates the columns? That's pretty much what you have to do

  SELECT CASE WHEN SunName = 'NOR' THEN sContents ELSE NULL END NOR,
         CASE WHEN SunName = 'SAR' THEN sContents ELSE NULL END SAR,
         CASE WHEN SunName = 'SAD' THEN sContents ELSE NULL END SAD,
         CASE WHEN SunName = 'RIV' THEN sContents ELSE NULL END RIV,
         CASE WHEN SunName = 'CUN' THEN sContents ELSE NULL END CUN,
         sId
   FROM t2

You can write a stored procedure that uses string processing and MySQL's PREPARE statement to synthesize that sort of query. That's a reasonably big job. Advice here.

Upvotes: 1

Related Questions