sql - mysql query to dynamically convert row data to columns

Question

I am working on a pivot table query. The schema is as follows

Sno, Name, District

The same name may appear in many districts eg take the sample data for example

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

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

Name CA JB MN LP
Mike 3 0 0 1
Proctor 0 2 2 0
Luke 0 0 3 0

i wrote the following query for this

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

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.

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?

I ask so because the output of the query "select distinct(districts) from district_details" will return me a single column having district name on each row, which i will like to be transposed to the column.

Answer 1

You simply cannot have a static SQL statement returning a variable number of columns. You need to build such statement each time the number of different districts changes. To do that, you execute first a

SELECT DISTINCT District FROM district_details;

This will give you the list of districts where there are details. You then build a SQL statement iterating over the previous result (pseudocode)

statement = "SELECT name "

For each row returned in d = SELECT DISTINCT District FROM district_details 
    statement = statement & ", SUM(IF(District=""" & d.District & """,1 ,0)) AS """ & d.District & """" 

statement = statement & " FROM district_details GROUP BY name;"

And execute that query. You'll then need have to handle in your code the processing of the variable number of columns