0

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?

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.

  • This kind of approach does work if the set of rows to be transposed as columns is limited and relatively static, but it won't help if the need is to transpose all rows. The exact need is not made clear in the question, though. And I'd assume that SQL to work also on MySQL with possibly only minor modifications needed like working around the lack of DECODE with FIND_IN_SET as per dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html). – zagrimsan Oct 24 '13 at 13:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.