0

I am trying to make a cross tab pivot table. I need to get all teams from the database table dynamically on separate columns instead of specifying names of the teams in the query which I am currently doing. I have looked at another example i.e. MySQL pivot table query with dynamic columns.

At the moment I am receiving the results fine from the database but only by manually typing what column I need in the query. See the example below:

SELECT IFNULL(DATE( date_posted ), 'Total') AS DATE, 
SUM(CASE WHEN added_by LIKE '%Team One%' THEN 1 ELSE 0 END) AS Team1, 
SUM(CASE WHEN added_by LIKE '%Team Two%' THEN 1 ELSE 0 END ) AS Team2,
COUNT( added_by ) AS Daily_Total FROM teamdata WHERE status LIKE 
'%completed%' GROUP BY IFNULL(DATE( date_posted ), 'Total') DESC WITH ROLLUP;

Which displays 4 columns of DATE, Team1, Team2 and Daily_Total. But at a later stage there will be more columns to add in but to be done dynamically. I am trying to execute this prepared statement but to no success:

SET @sql = NULL;
SELECT GROUP_CONCAT( DISTINCT 
CONCAT(  
    'sum(CASE WHEN added_by = ''', 
    added_by,  
    ''' THEN 1 else ''-'' END) AS `', added_by,
      '`' 
    ) 
) into @sql 
FROM teamdata;

SET @sql 
= CONCAT('SELECT IFNULL(DATE( date_posted ), \'Total\') AS DATE, ', @sql, ' 
        from teamdata
        WHERE status = \'completed\' 
        GROUP BY IFNULL(DATE( date_posted ), \'Total\') DESC WITH ROLLUP');

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

Please could I get further help on this.

Community
  • 1
  • 1
TeyJohn
  • 11
  • 4
  • Your second `SET` statement needs to escape the internal quote characters in `'Total'` and `'completed'`, and you have two commas after DATE. – Bill Karwin Jan 13 '17 at 16:19
  • So how do I go about to escape the internal quote character. I have edited the two commas after the date my apologies @BillKarwin – TeyJohn Jan 13 '17 at 16:26
  • I don't understand why you would ask that, because you already did it in the first `SET` statement: use two single-quotes where you want one literal single-quote in your string. Or else use a backslash, like `\'`. Read http://dev.mysql.com/doc/refman/5.7/en/string-literals.html – Bill Karwin Jan 13 '17 at 17:43
  • Yes sorry you were right. I have edited the above code where I have added a backslash for 'Total' and 'completed'. – TeyJohn Jan 15 '17 at 18:29
  • The query has been executed successfully but it gives me these results of the query: PREPARE stmt FROM @sql ;# MySQL returned an empty result set (i.e. zero rows). EXECUTE stmt;# Rows: 132 DEALLOCATE PREPARE stmt;# MySQL returned an empty result set (i.e. zero rows). But there is no table to display the results of the different teams.@BillKarwin – TeyJohn Jan 15 '17 at 18:47
  • So is there any data in the table with status='completed'? – Bill Karwin Jan 15 '17 at 20:23
  • Yes there is rows that have status='completed' but should it not display a table after execution with columns of 'added_by' teams.@BillKarwin – TeyJohn Jan 16 '17 at 02:49
  • Should there be a stored procedure created to see the data or is because I'm running the query through phpmyadmin whereby it is not displaying the results? – TeyJohn Jan 16 '17 at 04:01
  • You weren't clear in your description how you were executing the statements. If you're using phpmyadmin, you should know that every page load of a PHP application uses a new connection to the database server, so you can't execute a prepared statement you created on a prior page load. You'll have to write this as an SQL script or a stored procedure. – Bill Karwin Jan 16 '17 at 15:56

0 Answers0