Training, Open Source computer languages

This is page http://www.wellho.net/forum/The-MySQ ... abase/Transform-Pivot-tables.html

Our email: info@wellho.net • Phone: 01144 1225 708225

 
For 2023 (and 2024 ...) - we are now fully retired from IT training.
We have made many, many friends over 25 years of teaching about Python, Tcl, Perl, PHP, Lua, Java, C and C++ - and MySQL, Linux and Solaris/SunOS too. Our training notes are now very much out of date, but due to upward compatability most of our examples remain operational and even relevant ad you are welcome to make us if them "as seen" and at your own risk.

Lisa and I (Graham) now live in what was our training centre in Melksham - happy to meet with former delegates here - but do check ahead before coming round. We are far from inactive - rather, enjoying the times that we are retired but still healthy enough in mind and body to be active!

I am also active in many other area and still look after a lot of web sites - you can find an index ((here))
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.

© WELL HOUSE CONSULTANTS LTD., 2024: 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