| how to transform one row to multi-row with sql clause [message #406463] |
Thu, 04 June 2009 02:42  |
slightleaves
Messages: 15
Registered: August 2007
Location: china
|
Junior Member |
|
|
I'll transform a string such as 'a,b,c,d' to a multi-row resultset with sql clause.The length of the string is not sure,for example the string may be 'a,b,c,d,e,f,g'.
The resultset should be as belows
anyone who can tell how to write sql to complete this requirment,thanks.
CREATE TABLE aa_test(
a VARCHAR2(10)
);
INSERT INTO aa_test(a)VALUES('a');
INSERT INTO aa_test(a)VALUES('b');
INSERT INTO aa_test(a)VALUES('c');
INSERT INTO aa_test(a)VALUES('d');
COMMIT;
SQL> SELECT * FROM aa_test;
A
----------
a
b
c
d
|
|
|
|
|
|
| Re: how to transform one row to multi-row with sql clause [message #406465 is a reply to message #406464] |
Thu, 04 June 2009 02:52  |
slightleaves
Messages: 15
Registered: August 2007
Location: china
|
Junior Member |
|
|
| pablolee wrote on Thu, 04 June 2009 02:45 | Post what you have tried, we are not here to complete your work (or homework assignment) for you, we will be glad to help if you show that you are prepared to do some work yourself. Read the Posting guidelines as to how to post a proper test case.
Thanks
|
but.......it's not my homework assignment,it's the problem I meet in the work,I don't know indeed how to figure it out
|
|
|
|
|
|
| Re: how to transform one row to multi-row with sql clause [message #406471 is a reply to message #406465] |
Thu, 04 June 2009 03:08 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
One way of doing it is to create a table type in SQL:create or replace type ty_212 as table of varchar2(30);
/
You can then use a query like SELECT column_value
FROM table(function_to_make_table('a,b,c,d,e'));
Alternatively, you could use a row generator technique such as SELECT level lv FROM DUAL CONNECT BY level <=10; |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
