title: Debugging MySQL pivot row into dynamic number of columns
body: <p>I recently came across the thread below, and it was very useful in building a dynamic SQL for MySQL.</p>
  
  <p><a href="https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns">MySQL pivot row into dynamic number of columns</a></p>
  
  <p>With that said, I did struggle with trying to debug the statement. Now for the real purpose of this post! To debug, I would run a Select on my variable containing the statement (Select @SQL). Then copy that result from the viewer windows and have the query analyzer review it. Once I did this, development really sped up. I am sure this is known by all the advance pro developers but for any newbies, I hope this help!</p>
  
  <p>My dynamic statement looks like this as a reference.</p>
  
  <pre><code>SET @sql = NULL;
  SET @@group_concat_max_len = 50000;
  SELECT
    GROUP_CONCAT(DISTINCT
      CONCAT(
        ' sum(
        case when symbol = ''',
        symbol,
        ''' then pctttlassets end) AS ',
        CONCAT(UPPER(ACode),'_',REPLACE(Symbol, '+', ''))
      )
    ) INTO @sql
  from trade_detail this;
  
  SET @sql = CONCAT('SELECT Distinct(main.port_code) as PortCode, ', @sql, ' 
  FROM trade_detail main GROUP BY main.Port_Code');
  
  SELECT @SQL;
  
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  </code></pre>