Re: Query Question

From: Mtek <mtek_at_mtekusa.com>
Date: Thu, 19 Jun 2008 13:03:30 -0700 (PDT)
Message-ID: <fff31b21-f8b5-4714-bf21-274992545138@x41g2000hsb.googlegroups.com>


On Jun 19, 2:40 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> Mtek schrieb:
>
>
>
> > On Jun 19, 1:21 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> >> Mtek schrieb:
>
> >>> Hi,
> >>> Is it possible to return all rows from a query on one line?
> >>> I have this query:
> >>> SELECT cnt || ':' || t_id
> >>> FROM (SELECT COUNT(*) cnt, t_id
> >>> FROM zc_body zb, zc_type zt
> >>> WHERE zb.t_id = zt.id
> >>> group by t_id);
> >>> It returns data like this:
> >>> 86:3
> >>> 994:7
> >>> 99:18
> >>> 38:27
> >>> 45:36
> >>> However, they want it in one line: 86:3,994:7,99:18,38:27,45:36
> >>> Can this be done?
> >>> Thanks!
> >>> John
> >> SQL> with t as (
> >> 2 select '86:3' cnt_t_id from dual union all
> >> 3 select '994:7' from dual union all
> >> 4 select '99:18' from dual union all
> >> 5 select '38:27' from dual union all
> >> 6 select '45:36' from dual
> >> 7 )
> >> 8 -- End test data
> >> 9 select wm_concat(cnt_t_id)
> >> 10 from t
> >> 11 ;
>
> >> WM_CONCAT(CNT_T_ID)
> >> --------------------------------------------------
> >> 86:3,994:7,99:18,38:27,45:36
>
> >> or search on asktom for "stragg"
>
> >> Best regards
>
> >> Maxim
>
> > Trying to work with your idea. Still gives everything on a separate
> > line. Although, I do not understand fully thw 'with' query. Got to
> > read on that......
>
> > with t as (
> > SELECT cnt || ':' || t_id cnt_t_id
> > FROM (SELECT COUNT(*) cnt, t_id
> > FROM zc_body zb, zc_type zt
> > WHERE zb.t_id = zt.id
> > group by t_id))
> > select cnt_t_id from t;
>
> > Also, I do not think WM_CONCAT is available in Oracle 10g??
>
> > John
>
> SQL> select * from v$version where rownum<=1;
>
> BANNER
> ----------------------------------------------------------------
> Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
>
> SQL> desc wm_concat
> FUNCTION wm_concat RETURNS VARCHAR2
> Argument Name Type In/Out Default?
> ------------------------------ ----------------------- ------ --------
> P1 VARCHAR2 IN
>
> "With" is not relevant to your question, i used it only to get the test
> data to demonstrate the query, simply put it is equivalent to an inline
> view which can be referenced as table, anyway, i appreciate your
> intention to read on that.
>
> Best regards
>
> Maxim

Strange,

SQL> select * from v$version where rownum<=1;

BANNER



Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi

Elapsed: 00:00:00.02
SQL> desc wm_concat
ERROR:
ORA-04043: object wm_concat does not exist

SQL> select owner from dba_objects where lower(object_name) = 'wm_concat';

no rows selected

Weird......

John Received on Thu Jun 19 2008 - 15:03:30 CDT

Original text of this message