CODEWITHSUNDEEP
mysqlsqlpivot-table
Nabil Sham
Nabil Sham

Reputation: 2345

Mysql pivot table with case

I am trying to pivot a state tax table,

source table 

+------------+-----------+------------------+----------+
| state_code | threshold | filing_status    | tax_rate |
+------------+-----------+------------------+----------+
| AK         |      9000 | Head of Househol |    0.000 |
| AK         |     19000 | Married          |    0.000 |
| AK         |      8000 | Single           |    0.000 |
| AL         |         0 | Head of Househol |    2.000 |
| AL         |         0 | Married          |    2.000 |

select 

select 
    P.state_code, 
    P.tax_rate, 
    case when P.filing_status = 'Married' then threshold  end as 'Married', 
    case when P.filing_status = 'Single' then threshold  end as 'Single'  
from state_tax_bracket P;

will return multiple row per ratio

+------------+----------+-------------------+---------+---------+
| state_code | tax_rate | Head of Household | Married | Single  |
+------------+----------+-------------------+---------+---------+
| AR         |    7.000 |             32600 |    NULL |    NULL |
| AR         |    7.000 |              NULL |   52600 |    NULL |
| AR         |    7.000 |              NULL |    NULL |   21600 |

But desired result is one row per ratio and all status values 

+------------+----------+-------------------+---------+---------+
| state_code | tax_rate | Head of Household | Married | Single  |
+------------+----------+-------------------+---------+---------+
| AR         |    7.000 |             32600 |   52600 |   21600 |

without procedure.

Upvotes: 0

Views: 96

Answers (2)

Nabil Sham
Nabil Sham

Reputation: 2345

In case someone is looking for the same I have found the solution:

select 
t.state_code, 
t.state_name, 
sum(t.Single) as 'single',
sum(Married) as 'married',
sum(`Head of Household`) as 'head',
t.tax_rate as 'tax_rate' 
from ( 
   select 
   P.state_code,
   S.state_name, 
   P.tax_rate,
   case when P.filing_status = 'Head of Househol' then threshold end as 'Head of Household', 
   case when P.filing_status = 'Married' then threshold  end as 'Married', 
   case when P.filing_status = 'Single' then threshold  end as 'Single'  
        from 
        state_tax_bracket P, 
        state_tax S 
        where 
        P.state_code = S.state_code ) 
     as t  
     group by tax_rate;

Upvotes: 1

ediardo
ediardo

Reputation: 157

You could use MAX and IFNULL like this:

MAX(IFNULL(threshold,NULL)).

Eddie.

Upvotes: 1

Related Questions