title: Dynamic pivot query using MySQL
body: <p>I have the following table with the details:</p>
  <p><strong>Example</strong>:</p>
  <pre><code> CREATE TABLE Table1
  (`PK` int, `Name` varchar(3), `Subject` varchar(9), `Grade` varchar(1));
  
  INSERT INTO Table1
  (`PK`, `Name`, `Subject`, `Grade`)
  VALUES
  (1, 'Bob', 'Math', 'A'),
  (2, 'Bob', 'History', 'B'),
  (3, 'Bob', 'Language', 'C'),
  (4, 'Bob', 'Biology', 'D'),
  (5, 'Sue', 'History', 'C'),
  (6, 'Sue', 'Math', 'A'),
  (7, 'Sue', 'Music', 'A'),
  (8, 'Sue', 'Geography', 'C');
  </code></pre>
  <p>Now I want to write a store procedure through which I want to pivot the table.</p>
  <h1>Attempt</h1>
  <pre><code> DELIMITER $$
   create PROCEDURE sptest1(IN nm varchar(50),IN sub varchar(50))
   begin
   SET @sql = NULL;
  
   SELECT GROUP_CONCAT(DISTINCT
       CONCAT('MAX(CASE WHEN ', nm ,' = ''', nm,
       ''' THEN grade END) `', nm, '`'))
   INTO @sql
   FROM table1;
  
   SET @sql = CONCAT('SELECT ', sub ,',', @sql, ' 
                   FROM table1 
                  GROUP BY ', sub ,'');
  select @sql;
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  
  END$$
  DELIMITER ;
  </code></pre>
  <p>Calling Function:</p>
  <pre><code>call sptest1('name','subject')
  </code></pre>
  <p>But getting wrong result:</p>
  <pre><code> subject    name
   ----------------
   Biology    null
   Geography  null
   History    null
   Language   null
   Math       null
   Music      null
  </code></pre>
  <p>Expected Result:</p>
  <pre><code> subject    Bob     Sue
   -------------------------
   Biology    D       null    
   Geography  null    C
   History    B       C
   Language   C       null
   Math       A       A
   Music      null    A
  </code></pre>