mysql 项目合并相同公司主体然后行列转置

先根据项目分组，不同的公司主体合并，逗号隔开，

select er.NAME                                                                AS CITY,
             epbi.PROJECT_NAME,
             PROE.TYPE,
             GROUP_CONCAT(PROE.NAME ORDER by er.NAME, epbi.PROJECT_NAME, PROE.TYPE) AS NAME,
             GROUP_CONCAT(GP.NAME ORDER by er.NAME, epbi.PROJECT_NAME, PROE.TYPE)   AS GPNAME
      from eju_project_basic_info epbi
               left join eju_region er on epbi.CITY_ID = er.REGION_ID
               inner JOIN (select ASS.PROJECT_ID,
                                  PROE.NAME,
                                  PROE.PROJECT_ENTERPRISE_ID,
                                  case ASS.TYPE when 0 then '投资' when 1 then '开发' when 2 then '运营' else null end as TYPE
                           from EJU_PROJECT_ENTERPRISE PROE,
                                EJU_PROJECT_ENTERPRISE_ASSOCIATION ASS
                           where ASS.ENTERPRISE_ID = PROE.PROJECT_ENTERPRISE_ID) PROE ON PROE.PROJECT_ID = EPBI.PROJECT_ID
               LEFT JOIN (select GP.GROUP_ENTERPRISE_ID, gp.NAME, REA.PROJECT_ENTERPRISE_ID
                          from EJU_GROUP_ENTERPRISE GP,
                               EJU_ENTERPRISE_RELATION REA
                          where GP.GROUP_ENTERPRISE_ID = REA.GROUP_ENTERPRISE_ID) GP ON GP.PROJECT_ENTERPRISE_ID = PROE.PROJECT_ENTERPRISE_ID
      where VALIDATION != 3
      GROUP BY er.NAME, epbi.PROJECT_NAME, PROE.TYPE

效果如下，利用 GROUP_CONCAT实现，同一个项目合并不同的公司

 利用行列转置，实现投资，运营，开发的转置， 

SELECT CITY,PROJECT_NAME,
       max(case when TYPE='投资' then NAME else '' end ) as '投资',
       max(case when TYPE='投资' then GPNAME else '' end ) as '投资主体',
       max(case when TYPE='运营' then NAME else '' end ) as '运营',
       max(case when TYPE='运营' then GPNAME else '' end ) as '运营主体',
       max(case when TYPE='开发' then NAME else '' end ) as '开发',
       max(case when TYPE='开发' then GPNAME else '' end ) as '开发主体'
FROM (select er.NAME                                                                AS CITY,
             epbi.PROJECT_NAME,
             PROE.TYPE,
             GROUP_CONCAT(PROE.NAME ORDER by er.NAME, epbi.PROJECT_NAME, PROE.TYPE) AS NAME,
             GROUP_CONCAT(GP.NAME ORDER by er.NAME, epbi.PROJECT_NAME, PROE.TYPE)   AS GPNAME
      from eju_project_basic_info epbi
               left join eju_region er on epbi.CITY_ID = er.REGION_ID
               inner JOIN (select ASS.PROJECT_ID,
                                  PROE.NAME,
                                  PROE.PROJECT_ENTERPRISE_ID,
                                  case ASS.TYPE when 0 then '投资' when 1 then '开发' when 2 then '运营' else null end as TYPE
                           from EJU_PROJECT_ENTERPRISE PROE,
                                EJU_PROJECT_ENTERPRISE_ASSOCIATION ASS
                           where ASS.ENTERPRISE_ID = PROE.PROJECT_ENTERPRISE_ID) PROE ON PROE.PROJECT_ID = EPBI.PROJECT_ID
               LEFT JOIN (select GP.GROUP_ENTERPRISE_ID, gp.NAME, REA.PROJECT_ENTERPRISE_ID
                          from EJU_GROUP_ENTERPRISE GP,
                               EJU_ENTERPRISE_RELATION REA
                          where GP.GROUP_ENTERPRISE_ID = REA.GROUP_ENTERPRISE_ID) GP ON GP.PROJECT_ENTERPRISE_ID = PROE.PROJECT_ENTERPRISE_ID
      where VALIDATION != 3
      GROUP BY er.NAME, epbi.PROJECT_NAME, PROE.TYPE) B
group by CITY,PROJECT_NAME

最终结果展示，