Trouble with temporary table. 1

[oppcos]

Hi all,
Here's what I'm trying to do. First, I have a table like this:

 _________   ________   _____   _____
[prime_key] [Field_ID] [Field] [Class]
 ---------   --------   -----   -----
         1         1A   'foo'     'A'
         2         2A   'bar'     'A'
         3         1A   'tst'     'B'
         4         3A   'hi.'     'B'

I am trying to create a sql statement that returns this data rearranged into a table that utilizes the Class for its headers so it looks like this:

 ________       _       _   
[Field_ID]     [A]     [B]
 --------   -----   -----
       1A   'foo'   'tst'
       2A   'bar'    null
       3A    null   'hi.'

I'm attempting to use a

CREATE TEMPORARY TABLE Temp SELECT ????

but can't seem to wrap my head around what I'll need to do to populate the temp table. The issue is there could be many different 'Class'es and I want the columns returned to be dynamic according to however many Classes are set and using the Field_ID for the primary key of the newly created table. Hope that makes sense (its late). Thanks for any help!

 

[TonyGroves]

You can't do that sort of thing using SQL alone, as the structure of the result set would depend not on the structure of the select statement, but on the data in its result set, which is unpredictable.

What you could do is to first do a select to find out what Class values are used, then using program code, assemble a SELECT statement using the known class values as column names. This might look something like:
[tt]
SELECT
field_id,
MAX(IF(class='A',field,NULL)) `A`,
MAX(IF(class='B',field,NULL)) `B`
FROM tblname
GROUP BY field_id
[/tt]

 

[oppcos]

Oh well, I was hoping to hide that complexity in a view, but I suppose that two passes like that isn't too bad. Thanks for helping me understand what limitation I was running up against.

 

[Erakis]

Thanks for your help.
I will re-transpose my data using programmation (C#).