MYSQL How do I query to show data in separate columns from one field?

Question

sorry if my question is worded poorly, my SQL vocabulary is very poor.

So basically, suppose I had a table as so:

CREATE TABLE MYTABLE { item_id int AUTO_INCREMENT PRIMARY KEY, item_name VARCHAR(20) }; INSERT INTO MYTABLE (item_name) VALUES ('Fred'), ('Arthur'), ('Jane');

Now i want to fetch data that lists "Fred", "Arthur", and "Jane" into three separate columns. Is there a way to do this?

Answer 1

ORACLE, NOT MYSQL

CREATE TABLE MYTABLE ( item_id int PRIMARY KEY, item_name VARCHAR(20) ); 

INSERT INTO MYTABLE (item_id,item_name) VALUES (1,'Fred'); 

INSERT INTO MYTABLE (item_id,item_name) VALUES (2,'Arthur');

INSERT INTO MYTABLE (item_id,item_name) VALUES (3,'Jane');

select * from mytable;

**ITEM_ID ITEM_NAME**           
---------- --------------------
         1 Fred                
         2 Arthur              
         3 Jane                

3 rows selected.


select 
  item_id, 
  (select unique item_name
     from MYTABLE
     where item_name='Fred') as fred,  
  (select unique item_name
     from MYTABLE
     where item_name='Arthur') as Arthur,
  (select unique item_name
     from MYTABLE
     where item_name='Jane') as Jane
from MYTABLE;
/

   ITEM_ID FRED                 ARTHUR               JANE                
---------- -------------------- -------------------- --------------------
         1 Fred                 Arthur               Jane                
         2 Fred                 Arthur               Jane                
         3 Fred                 Arthur               Jane                

3 rows selected.

I am only learning but this works, remember subquery must return only one row, otherwise there will be error.

One more, this should work faster:

select 
 item_id,
 decode(item_name,'Fred',1,0) as Fred,
 decode(item_name,'Arthur',1,0) as arthur,
 decode(item_name,'Jane',1,0) as jane
from MYTABLE;

  ITEM_ID       FRED      ARTHUR        JANE
---------- ---------- ---------- ----------
         1          1          0          0
         2          0          1          0
         3          0          0          1

3 rows selected.

Hope this helps.