Dynamic mysql pivot table failing

Question

CREATE TABLE `tUSER` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `id_number` varchar(20) NOT NULL,
  `first_names` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL
  PRIMARY KEY (`id`),
  INDEX( `id_number`, `first_names`, `last_name`)
)

CREATE TABLE `tPROFILE` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `tUSER_id` bigint(20) DEFAULT NULL,
  `tTYPES_id` bigint(20) DEFAULT NULL,
  `value` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_index` (`tUSER_id`,`tTYPES_id`),
  INDEX( `tUSER_id`, `tTYPES_id`, `value`)
)

CREATE TABLE `tTYPES` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `type` varchar(100) NOT NULL DEFAULT '',
  `description` varchar(255) NOT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  FULLTEXT( `type`, `description`)
)

Above is the schema is need to get a pivot table from when i'm given and ID number or a cellphone number (Cellphone is a record in the tTYPES table)

My static pivot works because i have populated the table and know the types

select tUSER.*,
  max(CASE WHEN tTYPES.type = 'msisdn' THEN tPROFILE.value ElSE 0 END) AS 'msisdn',
  max(CASE WHEN tTYPES.type = 'network' THEN tPROFILE.value ELSE 0 END) AS 'network',
  max(CASE WHEN tTYPES.type = 'points' THEN tPROFILE.value ELSE 0 END) AS 'points',
  max(CASE WHEN tTYPES.type = 'card number' THEN tPROFILE.value ELSE 0 END) AS 'card number',
  max(CASE WHEN tTYPES.type = 'gender' THEN tPROFILE.value ELSE 0 END) AS 'gender'
 from e1m_test.tUSER 
inner join e1m_test.tPROFILE on tPROFILE.tUSER_id = tUSER.id
inner join e1m_test.tTYPES on tPROFILE.tTYPES_id = tTYPES.id
where tUSER.id = 312

I need a dynamic pivot query because i wont always know the types Below is my current effort but it tells me of a syntax error that I'm not able to solve

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT 
    CONCAT('max(CASE WHEN tTYPES.type="', 
    tTYPES.type, '" THEN tPROFILE.value ELSE 0 END) AS '
    , tTYPES.type))
INTO @sql
FROM
  tTYPES, tPROFILE;

SET @sql = CONCAT('select tUSER.*, ', @sql, ' 
    from tUSER 
    left join tPROFILE on tPROFILE.tUSER_id = tUSER.id
    left join tTYPES on tPROFILE.tTYPES_id = tTYPES.id
    where tUSER.id = 312');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

The error

09:54:06    PREPARE stmt FROM @sql  Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'number,max(CASE WHEN tTYPES.type="gender" THEN tPROFILE.value ELSE 0 END) AS gen' at line 1  0.141 sec

Any help or direction would be welcomed

Answer 1

Presumably, there are types in tTYPE that conflict with MySQL keywords (number, for example, is a language keyword). You would need to quote these identifiers, using backticks.

Further suggestions:

• I don't see why you would need table tPROFILE in the query that generates the conditional expressions; accordignly, distinct seems unnecessary in group_concat()

• I would recommend using single quotes for string literals rather than double quotes - although this is allowed in MySQL, it deviates from ANSI SQL standard, for no value added (you just need to properly escape the embedded single quotes)

• there is no need to concat() within group_concat(); the aggregate functions happily accepts a list of arguments, and seemlessly concatenates them

So:

SET @sql = NULL;
SELECT GROUP_CONCAT( 
    'MAX(CASE WHEN tTYPES.type=''', tTYPES.type, 
    ''' THEN tPROFILE.value ELSE 0 END) AS `', tTYPES.type, '`'
) INTO @sql
FROM tTYPES;

SET @sql = CONCAT('select tUSER.*, ', @sql, ' 
    from tUSER 
    left join tPROFILE on tPROFILE.tUSER_id = tUSER.id
    left join tTYPES on tPROFILE.tTYPES_id = tTYPES.id
    where tUSER.id = 312'
);

-- debug your query before running it!
-- SELECT @sql;           
           
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;