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.
