title: how to use MySQL for a dynamic pivot table or crosstab
body: <p>I know this question or a variance thereof has been asked multiple times, and I have even tried implementing a solution, but I am struggling in completing it.</p>
  
  <p>I have a very simple table with three data columns: Date1, Report_#, Name.  I would like to pivot it around Date and the various names as the header tab, and have all the report numbers appear below. </p>
  
  <p>So it would LOOK like this:</p>
  
  <pre><code>Report | Date   | Name                     Date   | Name1 | Name2 | Name3
  -----------------------                    ------------------------------
  1      | 4-5-12 | Name1                    4-5-12 | 1     | 2     | 3
  2      | 4-5-12 | Name2      -----&gt;        4-6-12 | 4     | 5     | 6
  3      | 4-5-12 | Name3                    4-7-12 | 7     | 8     | 9
  4      | 4-6-12 | Name1                    
  5      | 4-6-12 | Name2                    
  6      | 4-6-12 | Name3                    
  7      | 4-7-12 | Name1
  8      | 4-7-12 | Name2
  9      | 4-7-12 | Name3
  </code></pre>
  
  <p>I was able to get an idea of what to do from <a href="http://www.artfulsoftware.com/infotree/queries.php#78" rel="nofollow">http://www.artfulsoftware.com/infotree/queries.php#78</a> but I'm stuck.  </p>
  
  <p>I was able to isolate the column names manually and list the report # under each name, but I want to do  dynamically figure out the distinct names, make them column names, and list the corresponding reports.</p>
  
  <p>So I created a procedure that would find distinct names and output the correct code. Now I have a hard time plugging the results of the procedure onto the query.  And the link above does not help whatsoever, (it seems to skip that step).</p>
  
  <p>So here is the code for the manual way:</p>
  
  <pre><code>SELECT `DATE`, 
  GROUP_CONCAT(CASE `Name` WHEN 'Name1' THEN `Report` END) AS 'Name1', 
  GROUP_CONCAT(CASE `Name` WHEN 'Name2' THEN `Report` END) AS 'Name2' 
  FROM `report_db` GROUP BY `DATE` ORDER BY `DATE`;
  </code></pre>
  
  <p>And here is the code that dynamic prints the GROUP_CONCAT(... for all the distinct names in the database:</p>
  
  <pre><code>DROP PROCEDURE IF EXISTS writecountpivot;
  DELIMITER | 
  CREATE PROCEDURE writecountpivot( db CHAR(64), tbl CHAR(64), col CHAR(64) ) 
  BEGIN 
  DECLARE datadelim CHAR(1) DEFAULT '"'; 
  DECLARE singlequote CHAR(1) DEFAULT CHAR(39); 
  DECLARE comma CHAR(1) DEFAULT ','; 
  SET @sqlmode = (SELECT @@sql_mode); 
  SET @@sql_mode=''; 
    SET @sql = CONCAT( 'SELECT DISTINCT CONCAT(', singlequote, 
                       ',group_concat(IF(', col, ' = ', datadelim, singlequote, comma, 
                       col, comma, singlequote, datadelim, comma, '`IR NO`,null)) AS `',  
                       singlequote, comma, col, comma, singlequote, '`', singlequote,  
                       ') AS countpivotarg FROM ', db, '.', tbl, 
                       ' WHERE ', col, ' IS NOT NULL' ); 
    -- UNCOMMENT TO SEE THE MIDLEVEL CODE: 
    -- SELECT @sql;  
    PREPARE stmt FROM @sql; 
    EXECUTE stmt; 
    DROP PREPARE stmt; 
    SET @@sql_mode=@sqlmode; 
  END
  | 
  DELIMITER ; 
  CALL writecountpivot('database','`report_db`','name');
  </code></pre>
  
  <p>and the result of the code above would be this</p>
  
  <pre><code>,group_concat(IF(name = "Name1",`IR NO`,null)) AS `Name1`
  ,group_concat(IF(name = "Name2",`IR NO`,null)) AS `Name2`
  ,group_concat(IF(name = "Name3",`IR NO`,null)) AS `Name3`
  </code></pre>
  
  <p>** So how do I take this text and plug it to my SQL? How do I put the procedure and the query together?**</p>