How to aggregate data stored column-wise in a matrix table

I have a table, Ellipses (...), represent multiple columns of a similar type

TABLE: diagnosis_info
COLUMNS: visit_id,
patient_diagnosis_code_1 ... 
patient_diagnosis_code_100  -- char(100) with a value of ‘0’ or ‘1’

How do I find the most common diagnosis_code? There are 101 columns including the visit_id. The table is like a matrix table of 0s and 1s. How do I write something that can dynamically account for all the columns and count all the rows where the value is 1?

What I would normally do is not feasable as there are too many columns:

SELECT COUNT(patient_diagnostic_code_1), COUNT(patient_diagnostic_code_2),... FROM diagnostic_info WHERE patient_diagnostic_code_1 = ‘1’ and patient_diagnostic_code_2 = ‘1’ and ….

Then even if I typed all that out how would I select which column had the highest count of values = 1. The table is more column oriented instead of row oriented.

Solution 1:

Unfortunately your data design is bad from the start. Instead it could be as simple as:

patient_id, visit_id, diagnosis_code

where a patient with 1 dignostic code would have 1 row, a patient with 100 diagnostic codes 100 rows and vice versa. At any given time you could transpose this into the format you presented (what is called a pivot or cross tab). Also in some databases, for example postgreSQL, you could put all those diagnostic codes into an array field, then it would look like:

patient_id, visit_id, diagnosis_code (data type -bool or int- array)

Now you need the reverse of it which is called unpivot. On some databases like SQL server there is UNPIVOT as an example.

Without knowing what your backend this, you could do that with an ugly SQL like:

select code, pdc
from 
(
select 1 as code, count(*) as pdc
from myTable where patient_diagnosis_code_1=1
union
select 2 as code, count(*) as pdc
from myTable where patient_diagnosis_code_2=1
union
...
select 100 as code, count(*) as pdc
from myTable where patient_diagnosis_code_100=1
) tmp
order by pdc desc, code;

PS: This would return all the codes with their frequency ordered from most to least. You could limit to get 1 to get the max (with ties in case there are more than one code to match the max).