sql - Count number of values per id -

let's assume have table1:

id  value1  value2 value3 1   z       null    null 1   z       null    null 1   null    y       null 1   null    null    x 2   null    y       null 2   z       null    null  3   null    y       null 3   null    null    null  3   z       null    null 

and have table2:

    id       1       2       3   

i want count number of values in each column per id have output this. (ex. id 1 has 2 - z's, 1 y , 1 x)

 id value1 value2  value3     1   2     1      1        2   1      1     0      3   1      1     0     

what approach suggested?

i using oracle 12c

do group by, use count (which counts non-null values):

select id,        count(value1) value1,        count(value2) value2,        count(value3) value3 table1 group id 

edit:

if values not null '.' (or else), use case expressions conditional counting, like:

select id,        count(case when value1 <> '.' 1 end) value1,        count(case when value2 <> '.' 1 end) value2,        count(case when value3 <> '.' 1 end) value3 table1 group id