| ||||||
Transform / Pivot tables? Posted by enquirer (enquirer), 30 October 2003 My version of MySQL does not support TRANSFORM / PIVOT tables.Do you know if a later version does or another method to create a pivot table? In other words, how can I see Code:
as Code:
Posted by admin (Graham Ellis), 30 October 2003 MySQL 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 This page is a thread posted to the opentalk forum
at www.opentalk.org.uk and
archived here for reference. To jump to the archive index please
follow this link.
| ||||||
PH: 01144 1225 708225 • FAX: 01144 1225 793803 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho |