mysql行转列

案例一丶

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(c.coursenm = ''',
      c.coursenm,
      ''', s.scores, 0)) AS ''',
      c.coursenm, ''''
    )
  ) INTO @sql
FROM courses c;
 
SET @sql = CONCAT('Select st.stuid, st.stunm, ', @sql, 
                        ' From Student  st 
                        Left Join score s On st.stuid = s.stuid
                        Left Join courses c On c.courseno = s.courseno
                        Group by st.stuid');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

 

 

案例二丶

CREATE DEFINER=`cmsuser`@`%` PROCEDURE `proc_zz`(
IN _PlanArea varchar(50),
IN _ProjectId varchar(50),
IN _FloorNumber varchar(50),
IN _FormatsName varchar(50),
IN _TwoFormatsName varchar(50),
IN _BunkNo varchar(50),
IN _BrandAbbreviation varchar(50),
IN _BusinessManager varchar(50),
IN _keyWord varchar(50)
)
begin

SET @sql = NULL; 
SET @sqlwhere ='';

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(fl.LicenceName = ''',
      fl.LicenceName,
      ''', lm.LicenseValidityDate, null)) AS ',
      fl.LicenceName
    )
  ) INTO @sql
FROM he_operating_formatsandlicencerelational fl;

if _PlanArea is not null and _PlanArea !='' then
SET @sqlwhere=CONCAT(@sqlwhere," ","and s.PlanArea=","'",_PlanArea,"'");
end if;
if _ProjectId is not null and _ProjectId !='' then
SET @sqlwhere=CONCAT(@sqlwhere," ","and s.ProjectId=","'",_ProjectId,"'");
end if;
if _FloorNumber is not null and _FloorNumber !='' then
SET @sqlwhere=CONCAT(@sqlwhere," ","and s.FloorNumber=","'",_FloorNumber,"'");
end if;
if _FormatsName is not null and _FormatsName !='' then
SET @sqlwhere=CONCAT(@sqlwhere," ","and s.FormatsName=","'",_FormatsName,"'");
end if;
if _TwoFormatsName is not null and _TwoFormatsName !='' then
SET @sqlwhere=CONCAT(@sqlwhere," ","and s.TwoFormatsName=","'",_TwoFormatsName,"'");
end if;
if _BunkNo is not null and _BunkNo !='' then
SET @sqlwhere=CONCAT(@sqlwhere," ","and s.BunkNo=","'",_BunkNo,"'");
end if;
if _BrandAbbreviation is not null and _BrandAbbreviation !='' then
SET @sqlwhere=CONCAT(@sqlwhere," ","and s.BrandAbbreviation=","'",_BrandAbbreviation,"'");
end if;
if _BusinessManager is not null and _BusinessManager !='' then
SET @sqlwhere=CONCAT(@sqlwhere," ","and s.BusinessManager=","'",_BusinessManager,"'");
end if;
if _keyWord is not null and _keyWord !='' then
SET @sqlwhere=CONCAT(@sqlwhere," ",
                                        "and s.PlanArea=","'",_keyWord,"'",
                                        "or s.PlanArea=","'",_keyWord,"'",
                                        "or s.PlanArea=","'",_keyWord,"'"
);
end if;

SET @sql = CONCAT('SELECT s.StoreNo,s.PlanArea,s.ProjectName,s.FloorNumber,s.FormatsCode,s.FormatsName,s.TwoFormatsName
                    , s.BunkNo,s.BrandAbbreviation,s.BusinessManager,s.StoreManager
                    , s.StoreManagerPhone, ', @sql, ' FROM he_merchants_store s
                   LEFT JOIN he_operating_licencemanageinfo AS lm ON s.StoreNo = lm.StoreNo 
                                     LEFT JOIN he_operating_formatsandlicencerelational fl on s.FormatsCode=fl.FormatsCode
                                     WHERE 1=1',@sqlwhere,'   GROUP BY s.StoreNo,s.FormatsCode');

 prepare stmt from @sql;
 execute stmt;
deallocate prepare stmt;
end