Re: Query Question

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 19 Jun 2008 21:40:52 +0200
Message-ID: <485AB644.2080703@gmail.com>

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 Received on Thu Jun 19 2008 - 14:40:52 CDT