disperse relational structure of MySQL table

Question

Dear all,

I have a table that consists of id (auto_increment) number int (can contain values from 10 to 12) myvalue (varchar)

What I want to do is disperse the relational structure of this table for report purpose. I.e , I´d like to have something like:

id (auto_increment)

number10 (containing myvalue WHERE number=10)

number11 (containing myvalue WHERE number=11)

number12 (containing myvalue WHERE number=12)

I know that I can get the respective results by SELECT myvalue FROM mytable WHERE number = 10;

but I haven´t figured out how to write these three SELECT statements into one single table or view.

thx for any help in advance!

Answer 1

This might do what you need. You've not explained it very well though so it might not!

SELECT user,
MIN(CASE WHEN number = 10 then myvalue end) AS number10,
MIN(CASE WHEN number = 11 then myvalue end) AS number11,
MIN(CASE WHEN number = 12 then myvalue end) AS number12
FROM table
WHERE number IN (10,11,12)
GROUP BY user 

Answer 2

I don't get the "id number10 number11 number12" stuff, but if you want to select the rows with the number field matching a set of values, you can just do:

SELECT * FROM mytable WHERE number IN (10, 11, 12);

Or, alternatively, you can select a number range:

SELECT * FROM mytable WHERE number >= 10 AND number <= 12;

Edit 2:

Vin-G's got it. I was way off.

Answer 3

Something like this maybe?:

SELECT
    id,
    IF(number=10, myvalue, NULL) AS number10,
    IF(number=11, myvalue, NULL) AS number11,
    IF(number=12, myvalue, NULL) AS number12
FROM mytable