Prev: why ora1801
Next: Fresh Enterprise Manager Install Can't connect "Connection Closed"
From: Mtek on 3 Jul 2008 15:07

Hi,

I have a query which is this:

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);

The output is this:

101:18
49:27
47:36
378:9
118:10
76:15
28:16
38:19

However, they want the output like this:

101:18,49:27,47:36,378:9,118:10,76:15,28:16,38:19

Can't figure this one out. Do not seem to have any WM_CONCAT or
anything. we are on 10g r2.

Thanks everyone!

John


From: Maxim Demenko on 3 Jul 2008 15:23
Mtek schrieb:
> Hi,
>
> I have a query which is this:
>
> 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);
>
> The output is this:
>
> 101:18
> 49:27
> 47:36
> 378:9
> 118:10
> 76:15
> 28:16
> 38:19
>
> However, they want the output like this:
>
> 101:18,49:27,47:36,378:9,118:10,76:15,28:16,38:19
>
> Can't figure this one out. Do not seem to have any WM_CONCAT or
> anything. we are on 10g r2.
>
> Thanks everyone!
>
> John
>
>

Here are provided dozens of different techniques to perform string
aggregation:
http://www.sqlsnippets.com/en/topic-12291.html
http://oracle-base.com/articles/10g/StringAggregationTechniques.php
(at least, i don't know any other easy method which is not mentioned on
those links). Some may look more difficult than others. I hope, you can
pick up one which looks familiar for you and helps you to get desired
results.

Best regards

Maxim
From: Mtek on 7 Jul 2008 12:10
On Jul 3, 2:23 pm, Maxim Demenko <mdeme...(a)gmail.com> wrote:
> Mtek schrieb:
>
>
>
> > Hi,
>
> > I have a query which is this:
>
> > 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);
>
> > The output is this:
>
> > 101:18
> > 49:27
> > 47:36
> > 378:9
> > 118:10
> > 76:15
> > 28:16
> > 38:19
>
> > However, they want the output like this:
>
> > 101:18,49:27,47:36,378:9,118:10,76:15,28:16,38:19
>
> > Can't figure this one out. Do not seem to have any WM_CONCAT or
> > anything. we are on 10g r2.
>
> > Thanks everyone!
>
> > John
>
> Here are provided dozens of different techniques to perform string
> aggregation:http://www.sqlsnippets.com/en/topic-12291.htmlhttp://oracle-base.com/articles/10g/StringAggregationTechniques.php
> (at least, i don't know any other easy method which is not mentioned on
> those links). Some may look more difficult than others. I hope, you can
> pick up one which looks familiar for you and helps you to get desired
> results.
>
> Best regards
>
> Maxim

Thanks, but none of these really do what I want. Most of them work
under the assumption that there is a common value in the data, which
in this case there is not. I just want a single row from these two
joined tables........
From: Ronny on 8 Jul 2008 18:34
Try this:

SELECT SUBSTR (MAX (SYS_CONNECT_BY_PATH (us, ', ')), 3) as
your_string
FROM (SELECT us, ROW_NUMBER () OVER (ORDER BY us) rn
FROM (SELECT cnt_t_id AS us
FROM (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)) x)
START WITH rn = 1
CONNECT BY PRIOR rn = rn - 1;

No warranty - it's untested.
 | 
Pages: 1
Prev: why ora1801
Next: Fresh Enterprise Manager Install Can't connect "Connection Closed"