| how can transfer a column into a row with a single SELECT? [message #19847] |
Sat, 13 April 2002 05:55  |
Jeff
Messages: 63
Registered: July 1999
|
Member |
|
|
how can I transfer a column into a row with a single SELECT ?
for example:
a table with a field FIELD1 as below:
FIELD1
----------
a
b
c
d
HOW CAN I get a result of a row as below using a single
SELECT sentence?
a b c d
If you have the answer,contact me asap,pls.thanks
|
|
|
|
| Re: how can transfer a column into a row with a single SELECT? [message #19850 is a reply to message #19847] |
Sat, 13 April 2002 13:23  |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
As far ORACLE existed in the RDBMS world, no such query could be created in the ORACLE. To resolve your query, the new (resultant) table should be able to grow with columns as the number of rows increase in the first table. As per my knowledge it is not possible in a single SQL statement.
If it can be done in a PL/SQL, then the resultant table should be ALTERed and UPDATEd with a new column every time a new row added in the first table, which is very slow and not a good idea.
If any one comes up with a better idea or suggestion for this, hey, I am very eager to know about it too :)
Good luck :)
|
|
|
|
| Re: how can transfer a column into a row with a single SELECT? [message #19865 is a reply to message #19850] |
Mon, 15 April 2002 05:57 |
sridhar
Messages: 119
Registered: December 2001
|
Senior Member |
|
|
There is one way, but this is only possible if we know the number of rows in the table prior to building the SQL statement and suitable for small table with few number of rows,
Select max(decode( rownum,1, col_name,null ))c1,
max(decode( rownum,2, col_name,null ))c2,
max(decode( rownum,3, col_name,null ))c3,
max(decode( rownum,4, col_name,null ))c4
From ( select * from table_name order by col_name);
Thanks,
Sri
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
