MariaDB Pivot not working when column name are numbers

Question

I have this code (I took it from other SO threads):

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(property_name = ''',
      property_name,
      ''', value, NULL)) AS ',
      property_name
    )
  ) INTO @sql
FROM properties;
#INNER JOIN combustibles ON properties.property_name = combustibles.id_combustible
SET @sql = CONCAT('SELECT anio, mes, ', @sql, ' FROM properties GROUP BY anio, mes');

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

And it's doing their job, my table is:

table: properties
id  year    month   property_name   value
1   1       ene     a               345
2   1       ene     b               545
3   1       ene     c               65
4   1       feb     a               32
5   1       feb     b               57
6   1       feb     c               444
7   2       ene     a               123
8   2       ene     b               333
9   2       ene     c               12
10  2       feb     a               56
11  2       feb     b               565
12  2       feb     c               34

and I'm getting what I need:

year    month   a   b   c
1       ene     345 545 65
1       feb     32  57  444
2       ene     123 333 12
2       feb     56  565 34

But the problem is if the values of "property_name" are only NUMBERS, like this:

table: properties
id  year    month   property_name   value
1   1       ene     1               345
2   1       ene     2               545
3   1       ene     3               65

etc...

I get this error:

Err] 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 '1,MAX(IF(property_name = '2', value, NULL)) AS 2,MAX(IF(property_name = '3', val' at line 1

I NEED that those "property_name" value be numbers, because this is an example, but with my real data, those values are id's from another table. Any hint?

I did this sqlfiddle in case anyone need it: http://sqlfiddle.com/#!9/f2fe9d/2 — pmiranda, Oct 24 '17 at 18:53
I get it, with: `CONCAT( 'MAX(IF(property_name = ''', property_name, ''', value, NULL)) AS ', CONCAT("`",property_name,"`") )` — pmiranda, Oct 24 '17 at 19:25
Try it by using `'` for some pairs; `"` for other pairs; it is confusing to use `'''`. — Rick James, Oct 27 '17 at 13:50
@RickJames Can you put an example? I have syntax error when trying to use " in those ''' parts — pmiranda, Oct 30 '17 at 20:35
`CONCAT( "MAX(IF(property_name = '", property_name, "', value, NULL)) AS ", CONCAT('",property_name,'") )` — Rick James, Oct 30 '17 at 20:39
[Err] 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 '") — pmiranda, Oct 30 '17 at 20:42

Paul Maxwell · Answer 1 · 2017-10-31T02:13:12.197

CREATE TABLE IF NOT EXISTS `properties` (
  `id` int(6) unsigned NOT NULL,
  `year` int(6) unsigned NOT NULL,
  `month` varchar(200) NOT NULL,
  `property_name` varchar(200) NOT NULL,
  `value` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `properties` (`id`, `year`, `month`, `property_name`, `value`) VALUES
("1","1","a","345","ene"),
("2","1","b","545","ene"),
("3","1","c","65","ene"),
("4","1","a","32","feb"),
("5","1","b","57","feb"),
("6","1","c","444","feb"),
("7","2","a","123","ene"),
("8","2","b","333","ene"),
("9","2","c","12","ene"),
("10","2","a","56","feb"),
("11","2","b","565","feb"),
("12","2","c","34","feb");

select group_CONCAT(p)
from (
    select distinct concat('MAX(IF(property_name = '''
        ,property_name
        ,''', value, NULL)) AS `'
        ,property_name
        ,'`') p
    from properties
    ) d

| group_CONCAT(p)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| :---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| MAX(IF(property_name = '345', value, NULL)) AS `345`,MAX(IF(property_name = '545', value, NULL)) AS `545`,MAX(IF(property_name = '65', value, NULL)) AS `65`,MAX(IF(property_name = '32', value, NULL)) AS `32`,MAX(IF(property_name = '57', value, NULL)) AS `57`,MAX(IF(property_name = '444', value, NULL)) AS `444`,MAX(IF(property_name = '123', value, NULL)) AS `123`,MAX(IF(property_name = '333', value, NULL)) AS `333`,MAX(IF(property_name = '12', value, NULL)) AS `12`,MAX(IF(property_name = '56', value, NULL)) AS `56`,MAX(IF(property_name = '565', value, NULL)) AS `565`,MAX(IF(property_name = '34', value, NULL)) AS `34` |

dbfiddle here

MariaDB Pivot not working when column name are numbers

1 Answers1