Transform / Pivot tables?
Posted by enquirer (enquirer), 30 October 2003My version of MySQL does not support TRANSFORM / PIVOT tables.
Do you know if a later version does or another method to create a
In other words, how can I see
Posted by admin (Graham Ellis), 30 October 2003MySQL has a more limited feature set than many databases (but more features *are* being added at new releases). Unless it has been sneaked in and I've failed to notice it, it does not have support for transforms as you describe them ... I did a double check by searching the MySQL web site for the words "transform" and "pivot" and also checking the indexes of the most recent books, and I drew a blank.
I suspect that one of the reasons that pivot tables aren't part of the offering is that, unusually, adding rows a table can cause the need for additional columns to be added to the pivot, which is rather different to usual database definition standards ....
How would I tackle the task? I would write a function to use in the client, and I would write it generalised enough to deal with any pivot cases I might have ... so it would be in a file that I could include in all my pages.
If you're shuddering at the efficiency of this, and if you have a very high proportion of lookups to data changes (probably the case if this particular data / enquiry is made available on a public web site) then you might like to consider having your data change client generate an extra table (a cache) within MySQL so that enquiries are efficient. Whether you remake the whole of this cache table every time data changes, or have code that can update it (more complex to write, but more efficient) would be up to you. ..... and ..... yes, I am aware that having an extra cache table breaks the principles of design of a true relational database
PH: 01144 1225 708225 • FAX: 01144 1225 899360 • EMAIL: email@example.com • WEB: http://www.wellho.net • SKYPE: wellho