Home » SQL & PL/SQL » SQL & PL/SQL » how to transform one row to multi-row with sql clause (Oracle9i Enterprise Edition Release ,9.2.0.4.0, Windwos XP)
how to transform one row to multi-row with sql clause [message #406463] Thu, 04 June 2009 02:42 Go to next message
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 #406464 is a reply to message #406463] Thu, 04 June 2009 02:45 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
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
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 Go to previous messageGo to next message
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 #406467 is a reply to message #406463] Thu, 04 June 2009 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a FAQ.
Search for "varying in list" or "string list" or the like.

Regards
Michel
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 Go to previous message
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);
/
, and a function that will split your string down and populate a table of this type.

You can then use a query like
SELECT column_value
FROM   table(function_to_make_table('a,b,c,d,e'));
to get your rows.

Alternatively, you could use a row generator technique such as
SELECT level lv FROM DUAL CONNECT BY level <=10;
to generate you additional rows for your result set and use SUBSTR/INSTR to get the term out of the string.
Previous Topic: How to connect table from different server using plsql
Next Topic: Cost of execution
Goto Forum:
  


Current Time: Mon Dec 05 08:42:28 CST 2016

Total time taken to generate the page: 0.04671 seconds