Query Question [Oracle]

Prev: driving_site and local function call
Next: Role/Security Question

From: Mtek on 19 Jun 2008 13:42

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

From: Shakespeare on 19 Jun 2008 14:10

"Mtek" <mtek(a)mtekusa.com> schreef in bericht
news:c80264dd-be17-4754-a00e-483b4182e855(a)l28g2000prd.googlegroups.com...
>
> 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
>

Give 'them' a text editor.

Shakespeare

From: Maxim Demenko on 19 Jun 2008 14:21

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

From: Mtek on 19 Jun 2008 15:07

On Jun 19, 1:21 pm, Maxim Demenko <mdeme...(a)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;

From: Mtek on 19 Jun 2008 15:12

| Next | Last
Pages: 1 2 3
Prev: driving_site and local function call
Next: Role/Security Question