[H-GEN] SQL query needed

[Arjen Lentz]

[ Humbug *General* list - semi-serious discussions about Humbug and     ]
[ Unix-related topics. Posts from non-subscribed addresses will vanish. ]

Hi Paul & Paul,

On Wed, 2003-08-27 at 17:24, Paul Gearon wrote:
> However, I suspect that Paul may have wanted the columns for each 
> subject to be automatically generated (since "maths1" and "english1" are 
> both entries in the "Tests" table).  I don't actually know of an SQL way 
> to do this in MySql (Arjen, can you help me here?).  It is possible in 
> other databases, eg. <cough, cough> MSAccess does it with pivot queries.

Indeed MS Access can pivot rows into columns, but the syntax is gross.

There's no standard SQL way to do this in a single query. The reason is
that for any SELECT you need to specify which columns you want returned.
The number of colunms can't be changed dynamically based on the table
contents.


MySQL 4.1 introduces a function GROUP_CONCAT(), which you can use to
pivot rows into a single column using a specified delimiter, ordering,
etc.
So you could have a table
  1000, 1, A
  1002, 1, B
and the select result could be
  1000, 1, "A,B"

Quite elegant, really. Without this function, again the cleanest
solution is to just query as usual and concatenate the results in PHP.


Regards,
Arjen.
-- 
Arjen Lentz, Technical Writer, Trainer
Brisbane, QLD Australia
MySQL AB, www.mysql.com

Brisbane 3 November (5 days): Using & Managing MySQL Training
Training,Support,Licenses,T-shirts @ https://order.mysql.com/?marl


--
* This is list (humbug) general handled by majordomo at lists.humbug.org.au .
* Postings to this list are only accepted from subscribed addresses of
* lists 'general' or 'general-post'.  See http://www.humbug.org.au/