Good morning everyone,
I have this view called salesData which basically looks like this and I want to pivot it dynamically since it might get updated later on.
+-------------+------------+------+
| salesTotal | salesYear | |
+-------------+------------+------+
| 3016207 | 2008 | |
| 3079627 | 2009 | |
| 3133681 | 2010 | |
| 3102944 | 2011 | |
| 3126710 | 2012 | |
| 3123600 | 2013 | |
| 3116452 | 2014 | |
| 3175186 | 2015 | |
| 122371 | 2016 |
+-------------+------------+------+
I have designed the following query but it doesn't seem to work. MySQL workbench is giving me a weird error message :
Error Code: 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 ' MAX(CASE WHEN salesYear='2008 THEN 3016207 ELSE 0 END) AS 2008,MAX(CASE WHEN sa' at line 1
My query is as follows :
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN salesYear=''',salesYear,''' THEN ',salesTotal, ' ELSE 0 END) AS ',salesYear))
INTO @sql
FROM salesData;
SET @sql = CONCAT('SELECT, ', @sql, ' FROM salesData');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
I would be grateful if someone could give me a hint. Thanks a lot :))
Oh btw, the result I'm looking for is this :
| 2008 | 2009 | 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | |
+---------+---------+---------+---------+---------+---------+---------+---------+--+
| 3133681 | 3133681 | 3133681 | 3102944 | 3126710 | 3126710 | 3126710 | 3126710 | |
+---------+---------+---------+---------+---------+---------+---------+---------+--+
I get it using the following query but the whole point of this question is making that very query dynamic ;)) :
SELECT
MAX(CASE WHEN (salesYear='2010') THEN salesTotal ELSE 0 END) AS '2008',
MAX(CASE WHEN (salesYear='2010') THEN salesTotal ELSE 0 END) AS '2009',
MAX(CASE WHEN (salesYear='2010') THEN salesTotal ELSE 0 END) AS '2010',
MAX(CASE WHEN (salesYear='2011') THEN salesTotal ELSE 0 END) AS '2011',
MAX(CASE WHEN (salesYear='2012') THEN salesTotal ELSE 0 END) AS '2012',
MAX(CASE WHEN (salesYear='2012') THEN salesTotal ELSE 0 END) AS '2013',
MAX(CASE WHEN (salesYear='2012') THEN salesTotal ELSE 0 END) AS '2014',
MAX(CASE WHEN (salesYear='2012') THEN salesTotal ELSE 0 END) AS '2015',
MAX(CASE WHEN (salesYear='2012') THEN salesTotal ELSE 0 END) AS '2016'
FROM salesData
EDIT : I found the error. The salesYear in this expression is not being concatenated properly.
GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN salesYear=''',salesYear,''' THEN ',salesTotal, ' ELSE 0 END) AS ',salesYear))
When I replace it by a string value as shown below, the query works perfectly. Anyone knows how to put that salesYear variable between single quotes ?
GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN salesYear=''',salesYear,''' THEN ',salesTotal, ' ELSE 0 END) AS ','test'))
