Training, Open Source computer languages
PerlPHPPythonMySQLApache / TomcatTclRubyJavaC and C++LinuxCSS 
Search for:
Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
 
For 2021 - online Python 3 training - see ((here)).

Our plans were to retire in summer 2020 and see the world, but Coronavirus has lead us into a lot of lockdown programming in Python 3 and PHP 7.
We can now offer tailored online training - small groups, real tutors - works really well for groups of 4 to 14 delegates. Anywhere in the world; course language English.

Please ask about private 'maintenance' training for Python 2, Tcl, Perl, PHP, Lua, etc.
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:
AR1     1         20

AR1     1         21

AR1     1         22

AR2     1         20

AR3     1         21


as

Code:
       20        21        22

AR1     1         1         1

AR2     1

AR3                 1



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.

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2022: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 793803 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho