Re: Query Question

From: Mtek <mtek_at_mtekusa.com>
Date: Thu, 19 Jun 2008 12:07:55 -0700 (PDT)
Message-ID: <66fc5dd2-acfe-47e6-8318-758fb8bb23ac@i36g2000prf.googlegroups.com>


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; Received on Thu Jun 19 2008 - 14:07:55 CDT

Original text of this message