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 |
CodePudding user response：
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.