Not sure i was clear enough so heres my view as it stands

CREATE OR REPLACE FORCE VIEW "Q_ABSENCE" ("OWNERID", "OWNERTYPE", "TYPE", "ITEMCOUNT") AS
select distinct 'No',
count(*) as itemcount
from sick_absence
where ab_proccessed =2
and AB_COORDPROCESSED = 'No'
group by ab_owner

Which would return the following data

NO Count
10 2
5 4
4:3 2
2 1
234:23 4

I need it to return

No Count
10 2
5 4
4 2
3 2
2 1
234 4
23 4

thanks for your time

Kirk

try like this...for splitting values

with t as
(select '10:5' no,15 cnt from dual union all
select '11',20 from dual)
select distinct substr (s, instr(s,':',1,l) + 1 , instr(s,':',1,l+1) - instr(s,':',1,l) - 1 ) no,cnt
from (select cnt,':' || no || ':' s, level l from t connect by level <= length(no) - length(replace(no,':')) + 1)
order by cnt;

NO CNT

---

----------
10 15
5 15
11 20