column values in a row

Question

I have following table

    id    count   hour   age   range
    -------------------------------------
    0       5       10     61     10-200
    1       6       20     61     10-200
    2       7       15     61     10-200  
    5       9       5      61     201-300
    7       10      25     61     201-300
    0       5       10     62     10-20
    1       6       20     62     10-20
    2       7       15     62     10-20  
    5       9       5      62     21-30
    1       8       6      62     21-30
    7       10      25     62     21-30
    10      15      30     62     31-40

I need to select distinct values of column range I tried following query

Select distinct range as interval from table name where age  = 62;

its result is in a column as follows:

interval
----------
10-20
21-30
31-41

How can I get result as follows?

10-20, 21-30, 31-40

EDITED: I am now trying following query:

select sys_connect_by_path(range,',') interval
from
    (select distinct NVL(range,'0') range , ROW_NUMBER() OVER (ORDER BY RANGE) rn 

 from table_name where age = 62)

 where connect_by_isleaf = 1 CONNECT BY rn = PRIOR rn+1 start with rn = 1;

Which is giving me output as:

Interval
----------------------------------------------------------------------------
, 10-20,10-20,10-20,21-30,21-30, 31-40

guys plz help me to get my desired output.

Justin Cave Justin Cave · Accepted Answer · 2012-02-28T15:22:54

If you are on 11.2 rather than just 11.1, you can use the LISTAGG aggregate function

SELECT listagg( interval, ',' ) 
         WITHIN GROUP( ORDER BY interval )
  FROM (SELECT DISTINCT range AS interval
          FROM table_name
         WHERE age = 62)

If you are using an earlier version of Oracle, you could use one of the other Oracle string aggregation techniques on Tim Hall's page. Prior to 11.2, my personal preference would be to create a user-defined aggregate function so that you can then

SELECT string_agg( interval )
  FROM (SELECT DISTINCT range AS interval
              FROM table_name
             WHERE age = 62)

If you don't want to create a function, however, you can use the ROW_NUMBER and SYS_CONNECT_BY_PATH approach though that tends to get a bit harder to follow

with x as (
  SELECT DISTINCT range AS interval
          FROM table_name
         WHERE age = 62 )
select ltrim( max( sys_connect_by_path(interval, ','))
                keep (dense_rank last order by curr),
              ',') range
  from (select interval,
               row_number() over (order by interval) as curr,
               row_number() over (order by interval) -1 as prev
          from x)
connect by prev = PRIOR curr
  start with curr = 1

column values in a row

1 Answers