mysql把多行数据合并为一行,并且字段名自己定义

变为

 

SELECT policy_id,  
MAX(CASE fee_type WHEN '3' THEN before_tax_amount ELSE 0 END)  before_tax_amount,
MAX(CASE fee_type WHEN '3' THEN rate ELSE 0 END)  commission_rate,
MAX(CASE fee_type WHEN '3' THEN tax ELSE 0 END)  tax,
MAX(CASE fee_type WHEN '3' THEN actual_amount ELSE 0 END)  commission_actual_amount,
MAX(CASE fee_type WHEN '6' THEN actual_amount ELSE 0 END)  agent_actual_amount,
MAX(CASE fee_type WHEN '6' THEN rate ELSE 0 END)  agent_rate,
MAX(CASE fee_type WHEN '6' THEN payment_name ELSE '' END)  agent_payment_name,
MAX(CASE fee_type WHEN '6' THEN pay_time ELSE '' END)  agent_pay_time,
MAX(CASE fee_type WHEN '10' THEN actual_amount ELSE 0 END)  leader_actual_amount,
MAX(CASE fee_type WHEN '10' THEN rate ELSE 0 END)  leader_rate,
MAX(CASE fee_type WHEN '10' THEN payment_name ELSE '' END)  leader_payment_name,
MAX(CASE fee_type WHEN '10' THEN pay_time ELSE '' END)  leader_pay_time,
MAX(CASE fee_type WHEN '11' THEN actual_amount ELSE 0 END)  individual_actual_amount,
MAX(CASE fee_type WHEN '11' THEN rate ELSE 0 END)  individual_rate,
MAX(CASE fee_type WHEN '11' THEN payment_name ELSE '' END)  individual_payment_name,
MAX(CASE fee_type WHEN '11' THEN pay_time ELSE '' END)  individual_pay_time,
MAX(CASE fee_type WHEN '12' THEN actual_amount ELSE 0 END)  car_dealer_actual_amount,
MAX(CASE fee_type WHEN '12' THEN rate ELSE 0 END)  car_dealer_rate,
MAX(CASE fee_type WHEN '12' THEN payment_name ELSE '' END)  car_dealer_payment_name,
MAX(CASE fee_type WHEN '12' THEN pay_time ELSE '' END)  car_dealer_pay_time,
MAX(CASE fee_type WHEN '13' THEN actual_amount ELSE 0 END)  community_actual_amount,
MAX(CASE fee_type WHEN '13' THEN rate ELSE 0 END)  community_rate,
MAX(CASE fee_type WHEN '13' THEN payment_name ELSE '' END)  community_payment_name,
MAX(CASE fee_type WHEN '13' THEN pay_time ELSE '' END)  community_pay_time
FROM tbl_oper_policy_fee
GROUP BY policy_id