| Building a string based on multiple fields [message #1917] |
Tue, 11 June 2002 02:04  |
Michael
Messages: 61
Registered: October 1999
|
Member |
|
|
I have a problem which is currently being solved with a script, I was wondering if anyone knows a way to do it in SQL. It goes something like this:
The table looks something like this:
CREATE TABLE TEST (
ID NUMBER,
Page NUMBER,
length NUMBER,
formula VARCHAR2 (20)
)
And records look like this:
ID Page length formula
1 1 3 a+
1 2 4 b+c
1 3 3 +d=x
.....
There are lots of this records in the table.
What we need to do is compile then together based on the ID using the page field for the order into two fields so it looks something like this:
ID formula
1 a+b+c+d=x
Any ideas?
Thanks,
Michael.
|
|
|
|
| Re: Building a string based on multiple fields [message #1919 is a reply to message #1917] |
Tue, 11 June 2002 07:04  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
Yes. you can.
But anyhow uneed to create a function
SQL> select * from test20;
ID PAGE LENGTH FORMULA
---------- ---------- ---------- --------------------
1 1 3 a+
1 2 4 b+c
1 3 3 +d=x
SQL> get f1
1 create or replace function f1(n number) return varchar2 is
2 retval varchar2(200);
3 cursor c1 is select formula from test20 where id=n;
4 begin
5 for crec in c1 loop
6 if c1%rowcount=1 then
7 retval:= crec.formula;
8 else
9 retval:=retval||','||crec.formula;
10 end if;
11 end loop;
12 return retval;
13* end;
SQL> /
Function created.
SQL> ed
Wrote file afiedt.buf
1* select id,f1(id) formula from test20 group by id
SQL> /
ID FORMULA
---------- --------------------
1 a+,b+c,+d=x
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
