Re: Complicated query: help please

Sean,

Write a plsql function named say... "issue_status" which builds a

denormalized string of the status/ count for each name.

call it from the select name,issue_status(name) issue_status from

your_table.

This plsql function will have a cursor with select status,count(*) from

your_table where name=argument_name group by status;

define a local variable to hold the denormal string of status. Build this up

as you loop thru the cursor

return the local variable.

example untested....

create function issue_status(as_name varchar2) return varchar2

ls_status varchar2(2000);

begin

for lrec in (select status,count(*) status_count from your_table where

name=as_name group by status)

loop

ls_status := ls_status || lrec.status || ' ' || lrec.status_count;

end loop;

return ls_status;

end;

The header will be "issue_status" or whatever you name the column for your

function.

hth

Dave Fowler

Oracle DBA/Developer.

"Sean Dolan" <nospam_at_nowhere.com> wrote in message news:9dp87302qbf_at_enews3.newsguy.com...
> Oracle 8.1.7 - I need help with a query.
>
> I currently have a query that obtains results like this through a group by
> clause:
>
> Name Status Count
> --------------------------------------------------
> bob Closed 18
> bob Help 23
> bob New 4
>
>
> I am trying to achieve:
>
> Name Closed Help New
> -------------------------------------------
> Bob 18 23 4
>
> Easy if you know how many columns to build for (like "Closed", "Help",
> "New") and you can build a function that does a count on each status...
 but
> what if a 4th gets added ? What query takes this into account and gets
 the
> results in the format I am looking for?
>
>
> Thanks,
> Sean
>
>
>
Received on Mon May 14 2001 - 16:34:49 CDT