I've written this code for mysql pivot table:
SET @SQL = NULL;
SET @@group_concat_max_len = 6000;
SELECT GROUP_CONCAT( DISTINCT CONCAT( 'MAX(IF(questiondetails = \'', questiondetails, '\', answer, null)) AS \'', questiondetails, '\' ' )) INTO @SQL FROM wtfeedback;
SET @SQL = CONCAT( 'SELECT trialid, productsku, userkey, category, ', @SQL, ' FROM wtfeedback GROUP BY trialid' );
PREPARE stmt FROM @SQL;
EXECUTE stmt;
This works fine in Sequel Pro (mysql gui editor)
But when I paste into my php page to run this code it is showing a syntax error:
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 'SET @@group_concat_max_len = 6000; SELECT GROUP_CONCAT( DISTINCT CONCAT( 'MAX(I' at line 3
I'm struggling to see what the error might be.
Any ideas ? Thanks in advance.
MySQL doesn't use '' to escape single quotes. If you want to embed single quotes in your in-sql strings, then use \':
CONCAT('MAX(IF(questiondetails = \'', questiondetails, '\', answer, null)) AS "', questiondetails, '" ')
^^---------------------^^
From PHP, issue only one statement at a time. I deduce that this is the problem since the error is pointing at the beginning of the second SET.
Here is a stored proc to generate a pivot SELECT for you.
I was struggling with the same issue as Guy Murray, but fought my way out. Basically I made a stored procedure that lets you run a pivot table on selectable rows and columns, with optional filtering. It does so by first storing the result of a "group by" select query in a temp table, and then fiddling that to a pivot table with the "group_concat" function. Same trick as Guy does. The advantage is that it goes through the main table only once, which may save time if there are zillions of records in it.
Here is a sample table:
CREATE TABLE `Data` (
`Period` INT(2) NOT NULL,
`Product` VARCHAR(20) NOT NULL DEFAULT '',
`Amount` DOUBLE NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=latin1;
INSERT INTO `Data` (`Period`, `Product`, `Amount`)
VALUES
(1,'PrdA',15484),
(1,'PrdA',45454),
(1,'PrdB',478),
(2,'PrdB',985),
(2,'PrdB',741),
(2,'PrdB',985),
(3,'PrdA',7515),
(3,'PrdA',454),
(3,'PrdB',4584),
(2,'PrdB',445),
(1,'PrdB',669);
And this is the stored procedure. Additional comment in the code.
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `pivot`(
source VARCHAR(1000),
val VARCHAR(40),
rws VARCHAR(40),
cls VARCHAR(40),
filter VARCHAR(1000))
BEGIN
/*
Creates a pivot table from any table, view or SQL statement.
Mandatory: source, value, rows, and columns to be pivoted.
Optional filtering.
Sample call strings:
CALL pivot('data', 'amount', 'period', 'product', '');
CALL pivot('(select * from data)', 'amount', 'product', 'period', 'WHERE amount>1000');
*/
/*just to be sure*/
DROP TEMPORARY TABLE IF EXISTS temp1;
/*increase the value of group concat, otherwise the number of columns is very limited*/
SET SESSION group_concat_max_len = 100000;
/*perform a "select...group by" on the source and store it in a temp table1*/
SET @a=CONCAT(
'CREATE TEMPORARY TABLE temp1 (
SELECT ',
rws,' AS rows, ',
cls,' AS cols,
SUM(',val,') AS val
FROM ',source,' S ',
filter, '
GROUP BY '
,rws,', ',
cls,');'
);
PREPARE stmt FROM @a;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
/*use "distinct columns" from temp1 to make a text string @coltext, that contains the column statements, to be used in the final step
Produced text string looks like this: sum(CASE WHEN cols='PrdA' THEN val END) AS 'PrdA', sum(CASE WHEN cols='PrdB' THEN val END) AS 'PrdB' */
SELECT GROUP_CONCAT(
' SUM(CASE WHEN cols=\'',cols,'\' THEN val END) AS \'',cols,'\'')
INTO @coltext
FROM (SELECT DISTINCT(cols) AS cols FROM temp1) A;
/*build the final statement in @b*/
SET @b=CONCAT(
'SELECT
IFNULL(rows, \'Total\') AS ',rws,', '
,@coltext,',
SUM(val) AS Total
FROM temp1
GROUP BY
rows
WITH ROLLUP;');
/*and launch it*/
PREPARE stmt FROM @b;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
/*clean up*/
DROP TEMPORARY TABLE IF EXISTS temp1;
SET @a=NULL;
SET @b=NULL;
SET @coltext=NULL;
END;;
DELIMITER ;
The result looks like this:
period PrdA PrdB total
1 60938 1147 62085
2 NULL 3156 3156
3 7969 4584 12553
total 68907 8887 77794
Hope this shows up correctly on stack overflow. It's my first post here.
edit 2015-10-19: when reading others solutions here, I realised that the code could be cleaned up and improved: it's now free of any hardcoded references. Just plug it in any database and it will work.