Transpose of column to row

Question

SELECT
   -- '<=19'AS Age_Range,
   case when gender = 1 then 'male'
   when gender = 2 then 'female' 
   end as gender_cat,
SUM(CASE WHEN hypertension= '1' THEN 1 ELSE 0 END) AS hypertension,
SUM(CASE WHEN chronic_cardiac_disease= '1' THEN 1 ELSE 0 END) AS chronic_cardiac_disease
FROM form where gender is not null
group by gender_cat

The output is coming as Gender_Cat | hypertension| chronic_cardiac_disease

I need to transpose the column to row for the desired output as: Disease | Male | Female.

Any help would be appreciated!!!

Answer 1

If a given patient may have both diseases, then I would recommend union all:

select 'hypertension' as disease, sum(gender = 1) as male, sum(gender = 2) as female
from form
where hypertension = 1
select 'chronic_cardiac_disease', sum(gender = 1), sum(gender = 2)
from form
where chronic_cardiac_disease = 1

In very recent MySQL versions, you would use a lateral join:

select x.disease, sum(f.gender = 1) as male, sum(f.gender = 2) as female
from form f
cross join lateral (
    select 'hypertension' as disease , hypertension as flag
    union all select 'chronic_cardiac_disease', chronic_cardiac_disease
) x
where x.flag = 1
group by x.disease