| rows into a fix no.of column report ? [message #18862] |
Tue, 19 February 2002 03:36  |
krahuman
Messages: 18
Registered: January 2002
|
Junior Member |
|
|
thanks for your reply.
i want to generate a report as shown below from a ora 8i table
this is much clear format of the report and table structure.
i have used '|' to divide to columns
table structure(category,account)
two fields
1.category- one category
2.account- one account
category account
c1 | a1
c1 | a2
c1 | a3
... | ...
.. | ...
c1 | a150
c2 | b1
c2 | b2
c2 | b2
... ...
.... ...
..... ...
report format should be
two columns
1.category - one accounts
2.account - all the accounts falls under a one
category.
fix no.of accts in one raw
category | accounts
-------- --------
c1 | a1 a2 a3
c1 | a4 a5
c2 | b1 b2 b3
c2 | b4 b5 b6
c2 | b7 b8 b9
there are more than 100 accounts for each category.
there the number of accotnts in a report raw should
a unique no. in my example 3
thanks in advance.
kaleel
|
|
|
|
| Re: rows into a fix no.of column report ? [message #18865 is a reply to message #18862] |
Tue, 19 February 2002 03:46  |
Suresh Vemulapalli
Messages: 624
Registered: August 2000
|
Senior Member |
|
|
write a function and call that function from sql.
1) function code:
create or replace function f1(v varchar2) return varchar2 is
cursor c1 is select * from cat_act where category=v;
v1 varchar2(200);
begin
for crec in c1 loop
if c1%rowcount=1 then
v1 := v1||crec.account;
else
v1:= v1||','||crec.account;
end if;
end loop
return v1;
end;
2)sql stmt:
select category,f1(category) accounts from cat_act group by category;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
