SQL count distinct values appearing in different columns

Question

i want to count distinct values and report their occurrences in SQL. The thing is i need to count occurrences on different columns and then report, for each column, the number of occurrences per value. So, say i have data like this from a table called Result :

Id	OwnerId	PilotId	CoachId
A	1	2	3
B	2	2	3
C	3	1	3

Then my result should be :

Id	Owner	Pilot	Coach
1	1	1	0
2	1	2	0
3	1	0	3

I tried using COUNT function with unions to get each result individually and it works. But i want to have the results on the same row per Id. Can anyone help me with this ?

Answer 1

I think you want to unpivot the columns to rows, then count the occurences of each value. You can do:

select v.id,
    sum(case when v.col = 'owner' then 1 else 0 end) owner,
    sum(case when v.col = 'pilot' then 1 else 0 end) pilot,
    sum(case when v.col = 'coach' then 1 else 0 end) coach
from mytable t
cross apply (values (t.ownerid, 'owner'), (t.pilotid, 'pilot'), (t.coachid, 'coach')) as v(id, col)
group by v.id

Demo on DB Fiddle:

id | owner | pilot | coach
-: | ----: | ----: | ----:
 1 |     1 |     1 |     0
 2 |     1 |     2 |     0
 3 |     1 |     0 |     3