How to select distinct values and their occurences from a table in oracle?

I have a table with the following data example:

id	first_numbers	second_numbers
12	111	112
12	111	123
12	111	122
12	110	123
13	100	null
13	100	101
13	112	999
13	null	999

The expected result which I want is the following:

id	first_numbers	counts	second_numbers	counts
12	111	3	112	1
12	110	1	123	2
12	null	null	122	1
13	100	2	101	1
13	112	1	999	2

Solution

Your question is not relational. You are treating table values as if they were a flat text file or Excel sheet. I would recommend you review relational concepts and normalization.

Anyway, you can twist SQL's hand to do what you want, but it's not a natural solution. For example, you can do:

select
  coalesce(x.id, y.id) as id,
  coalesce(x.r, y.r) as r,
  x.f, x.c,
  y.s, y.c
from (
  select id, f, count(*) as c,
  row_number() over(partition by id order by f) as r from t group by id, f
) x
full join (
  select id, s, count(*) as c,
  row_number() over(partition by id order by s) as r from t group by id, s
) y on y.id = x.id and y.r = x.r
where f is not null or s is not null
order by id, r

Result:

 ID  R  F    C  S    C 
 --- -- ---- -- ---- - 
 12  1  110  1  112  1 
 12  2  111  3  122  1 
 12  3          123  2 
 13  1  100  2  101  1 
 13  2  112  1  999  2

See running example at db<>fiddle.