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

The following assumes you want matches of distinct (name/district) pairs. I.e. Luke/CA and Duke/CA would yield two results:

SELECT name, District, count(District) AS count
FROM district_details
GROUP BY District, name

If this is not the case simply remove name from the GROUP BY clause.

Lastly, notice that I switched sum() for count() as you are trying to count all of the grouped rows rather than getting a summation of values.

Answer 2

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

Answer 3

a) "For each " is not supported in MySQL stored procedures. b) Stored procedures cannot execute prepared statements from concatenated strings using so called dynamic SQL statements, nor can it return results with more than One distinct row. c) Stored functions cannot execute dynamic SQL at all.

It is a nightmare to keep track of once you got a good idea and everyone seems to debunk it before they think "Why would anyone wanna..."

I hope you find your solution, I am still searching for mine. The closes I got was

(excuse the pseudo code)

-> to stored procedure, build function that...

1) create temp table 2) load data to temp table from columns using your if statements 3) load the temp table out to INOUT or OUT parameters in a stored procedure as you would a table call... IF you can get it to return more than one row

Also another tip... Store your districts as a table conventional style, load this and iterate by looping through the districts marked active to dynamically concatenate out a querystring that could be plain text for all the system cares

Then use;

prepare stmName from @yourqyerstring; execute stmName; deallocate prepare stmName;

(find much more on the stored procedures part of the mysql forum too)

to run a different set of districts every time, without having to re-design your original proc

Maybe it's easier in numerical form. I work on plain text content in my tables and have nothing to sum, count or add up