Mysql多表联合查询，行转列

项目中，所有的字段是通过key和value的形式存放的，以至于导致查询某一个人的数据，需要进行判断，
一开始在网上找了很多，大部分都是 一样demo,
第一次尝试，结果不是我想要的

select id,project_id,
sum(if(`variable`='SITEID',variable_value,null)) as core_code,
sum(if(`variable`='CRFVER',variable_value,null)) as crf_version,
 create_time,update_time,create_by,update_by,is_deleted
from (
	SELECT
		crfSubject.id,
		crfSubject.project_id,
		crfSubject.centre_id,
		subDetails.variable,
		subDetails.variable_name,
		subDetails.variable_value,
		subData.create_time,
		subData.update_time,
		subData.create_by,
		subData.update_by,
		crfSubject.is_deleted 
	FROM
		edc_crf_subject AS crfSubject
		LEFT JOIN edc_crf_subject_data AS subData ON crfSubject.id = subData.subject_id
		LEFT JOIN edc_crf_subject_data_details AS subDetails ON subData.id = subDetails.subject_data_id 
	WHERE
		subData.crf_name = 'SUBJECT' 
		AND subDetails.is_deleted = '0' 
	) AS info 
GROUP BY
	id;
	

core_code是字符，但却搞成 了数字 ，问题就出在sum ,所以 如果不是计算类型的，最好不要用su

最后使用max

	
	select id,
	max(	if(variable='SUBJID',variable_value,null)) as sub_code,
	max(	if(variable='SITEID',variable_value,null)) as core_code,
max(	if(variable='SITENAME',variable_value,null)) as core_name,
max(	if(variable='STATUS',variable_value,1)) as sub_status,
max(	if(variable='CRFVER',variable_value,null)) as crf_version,
max(	if(variable='CRFSTS',variable_value,1)) as crf_status,
max(	if(variable='COUNTRY',variable_value,null)) as county,
max(	if(variable='INVNAM',variable_value,null)) as invnam,
max(	if(variable='STUDYID',variable_value,null)) as study_id,
create_time,create_by,update_time,update_by,is_deleted
from (
	SELECT
		crfSubject.id,
		crfSubject.project_id,
		crfSubject.centre_id,
		subDetails.variable,
		subDetails.variable_name,
		subDetails.variable_value,
		subData.create_time,
		subData.update_time,
		subData.create_by,
		subData.update_by,
		crfSubject.is_deleted 
	FROM
		edc_crf_subject AS crfSubject
		LEFT JOIN edc_crf_subject_data AS subData ON crfSubject.id = subData.subject_id
		LEFT JOIN edc_crf_subject_data_details AS subDetails ON subData.id = subDetails.subject_data_id 
	WHERE
		subData.crf_name = 'SUBJECT' 
		AND subDetails.is_deleted = '0') as base_info group by id;

得到想要的结果