MySQL and Crosstab queries

Anon

Can anyone offer suggestions for generating a crosstab query in mysql? I don't think it's nativly possible, but if someone has some code to hack one or any ideas, I'd love to hear them.

The more I require from my database, the more I wish I'd started my project with something other than mysql. 🙂

Tom

Anon

You can see an example of a hacked version at http://db.etree.org/users.php

My desired sql, which is incorrect sql, would be:

select userid, count(ref_artist = 1) as c1, count(ref_artist = 2) as c2, from shows, myshows where ref_shows = myshows_key group by ref_artist order by userid

So my tables:

myshows

userid
ref_shows

shows

shows_key
ref_artist

Hope that helps define the question better,
Tom

Anon

I'm not sure if you could get the data in the form you wanted (userid,
c1, c2) all in the same row without some subqueries and mysql doesn't
like them.

Well, you've got the basic data, so all you have to do is
write it the data to successive columns, and change rows
when you get to the next userid.

But wait! that assumes each userid has the same records
for each ref_artist, and this is probably not true. You
will probably need to write the entire output to a 2-dimensional
array, and then process that when finished.

Anon

Did you ever find an appropriate way to do a cross tabulation query using MySQL? I am running in to the same problem right now. For your querying you may want to go to http://www.mysql.com/news/article-71.html. There is a good article on cross tabulation with sums and counts. Unfortunately it did not work for my situation.

tivnet

http://www.mysql.com/articles/wizard/index.html