using database contents as column names

[Guy Bowden]

Hi,

I have two tables I'm trying to join up, and would like to know if the
following is possible:

table1 :
SELECT * from mediatype

+-------------+---------+
| mediatypeid | type |
+-------------+---------+
| 1 | MP3 |
| 2 | ra_low |
| 3 | ra_med |
| 4 | ra_high |
+-------------+---------+

Table 2:
SELECT trackid, mediatypeid, url from media

+---------+-------------+----------------+
| trackid | mediatypeid | url |
+---------+-------------+----------------+
| 1 | 1 | mymp3.mp3 |
| 1 | 2 | myothermp3.mp3 |
| 1 | 3 | busted.mp3 |
| 1 | 4 | newmp3.mp3 |
+---------+-------------+----------------+
What I'd like to be able to get out of these two tables is a result
like this :

+---------|-----------|----------------|------------|------------+
| trackid | MP3 | ra_low | ra_med | ra_high |
+---------|-----------|----------------|------------|------------+
| 1 | mymp3.mp3 | myothermp3.mp3 | busted.mp3 | newmp3.mp3 |
+---------|-----------|----------------|------------|------------+

Is there a way to do this completly in SQL? or i could employ PHP to
help out..

Any help greatly appreciated!

Cheers,
Guy

[SR]

On 3 Feb 2004 09:45:14 -0800, Guy Bowden wrote:

Hi, [...] What I'd like to be able to get out of these two tables is a result
like this :

+---------|-----------|----------------|------------|------------+
| trackid | MP3 | ra_low | ra_med | ra_high |
+---------|-----------|----------------|------------|------------+
| 1 | mymp3.mp3 | myothermp3.mp3 | busted.mp3 | newmp3.mp3 |
+---------|-----------|----------------|------------|------------+

Hi,

basically you want to obtain a pivot table. AFAIK, no SQL dialect except M$
Access's supports it *directly*, but there's something you can do, anyway:

First issue this query:

select concat(", max(if(mediatypeid=", mediatypeid, ", url, Null)) AS ",
type) from mediatype;

This will result in:

, max(if(mediatypeid=1, url, Null)) AS mp3
, max(if(mediatypeid=2, url, Null)) AS ra_low
, max(if(mediatypeid=3, url, Null)) AS ra_med
, max(if(mediatypeid=4, url, Null)) AS ra_high

Then use php to create the actual query string:

select trackid
, max(if(mediatypeid=1, url, Null)) AS mp3
, max(if(mediatypeid=2, url, Null)) AS ra_low
, max(if(mediatypeid=3, url, Null)) AS ra_med
, max(if(mediatypeid=4, url, Null)) AS ra_high
from media group by trackid;

et voila...

[SR]

On 3 Feb 2004 09:45:14 -0800, Guy Bowden wrote:

Hi, [...] What I'd like to be able to get out of these two tables is a result
like this :

+---------|-----------|----------------|------------|------------+
| trackid | MP3 | ra_low | ra_med | ra_high |
+---------|-----------|----------------|------------|------------+
| 1 | mymp3.mp3 | myothermp3.mp3 | busted.mp3 | newmp3.mp3 |
+---------|-----------|----------------|------------|------------+

Hi,

basically you want to obtain a pivot table. AFAIK, no SQL dialect except M$
Access's supports it *directly*, but there's something you can do, anyway:

First issue this query:

select concat(", max(if(mediatypeid=", mediatypeid, ", url, Null)) AS ",
type) from mediatype;

This will result in:

, max(if(mediatypeid=1, url, Null)) AS mp3
, max(if(mediatypeid=2, url, Null)) AS ra_low
, max(if(mediatypeid=3, url, Null)) AS ra_med
, max(if(mediatypeid=4, url, Null)) AS ra_high

Then use php to create the actual query string:

select trackid
, max(if(mediatypeid=1, url, Null)) AS mp3
, max(if(mediatypeid=2, url, Null)) AS ra_low
, max(if(mediatypeid=3, url, Null)) AS ra_med
, max(if(mediatypeid=4, url, Null)) AS ra_high
from media group by trackid;

et voila...

[SR]

On 3 Feb 2004 09:45:14 -0800, Guy Bowden wrote:

Hi, [...] What I'd like to be able to get out of these two tables is a result
like this :

+---------|-----------|----------------|------------|------------+
| trackid | MP3 | ra_low | ra_med | ra_high |
+---------|-----------|----------------|------------|------------+
| 1 | mymp3.mp3 | myothermp3.mp3 | busted.mp3 | newmp3.mp3 |
+---------|-----------|----------------|------------|------------+

Hi,

basically you want to obtain a pivot table. AFAIK, no SQL dialect except M$
Access's supports it *directly*, but there's something you can do, anyway:

First issue this query:

select concat(", max(if(mediatypeid=", mediatypeid, ", url, Null)) AS ",
type) from mediatype;

This will result in:

, max(if(mediatypeid=1, url, Null)) AS mp3
, max(if(mediatypeid=2, url, Null)) AS ra_low
, max(if(mediatypeid=3, url, Null)) AS ra_med
, max(if(mediatypeid=4, url, Null)) AS ra_high

Then use php to create the actual query string:

select trackid
, max(if(mediatypeid=1, url, Null)) AS mp3
, max(if(mediatypeid=2, url, Null)) AS ra_low
, max(if(mediatypeid=3, url, Null)) AS ra_med
, max(if(mediatypeid=4, url, Null)) AS ra_high
from media group by trackid;

et voila...