title: mysql query to dynamically convert row data to columns
body: <p>I am working on a pivot table query.
  The schema is as follows</p>
  
  <p>Sno, Name, District</p>
  
  <p>The same name may appear in many districts eg take the sample data for example</p>
  
  <pre><code>1 Mike CA
  2 Mike CA
  3 Proctor JB
  4 Luke MN
  5 Luke MN
  6 Mike CA
  7 Mike LP
  8 Proctor MN
  9 Proctor JB
  10 Proctor MN
  11 Luke MN
  </code></pre>
  
  <p>As you see i have a set of 4 distinct districts (CA, JB, MN, LP). Now i wanted to get the pivot table generated for it by mapping the name against districts</p>
  
  <pre><code>Name CA JB MN LP
  Mike 3 0 0 1
  Proctor 0 2 2 0
  Luke 0 0 3 0
  </code></pre>
  
  <p>i wrote the following query for this</p>
  
  <pre><code>select name,sum(if(District="CA",1,0)) as "CA",sum(if(District="JB",1,0)) as "JB",sum(if(District="MN",1,0)) as "MN",sum(if(District="LP",1,0)) as "LP" from district_details group by name
  </code></pre>
  
  <p>However there is a possibility that the districts may increase, in that case i will have to manually edit the query again and add the new district to it.</p>
  
  <p><strong>I want to know if there is a query which can dynamically take the names of distinct districts and run the above query. I know i can do it with a procedure and generating the script on the fly, is there any other method too?</strong> </p>
  
  <p>I ask so because the output of the query "<strong>select distinct(districts) from district_details</strong>" will return me a single column having district name on each row, which i will like to be transposed to the column.</p>