mysql 行转列

GROUP_CONCAT(m.step,if(um.is_complete=1,'已完成','未完成') ORDER BY m.step SEPARATOR '/') c1,

SELECT 
user_id,
-- 第一次下单的时间
GROUP_CONCAT(if(seq=1,create_date,NULL)) AS 1_day,
-- 第一次下单的gmv
SUM(IF(seq=1,gmv,0)) AS 1_gmv,
-- 第一次下单的公司id
GROUP_CONCAT(if(seq=1,company_id,NULL)) AS 1_company_id,
-- 第二次下单的时间
GROUP_CONCAT(if(seq=2,create_date,NULL)) AS 2_day,
-- 第二次下单的gmv
SUM(IF(seq=2,gmv,0)) AS 2_gmv,
-- 第二次下单的公司id
GROUP_CONCAT(if(seq=2,company_id,NULL)) AS 2_company_id
 FROM (
-- 以上的部分为第二层
SELECT t1.*,IF(@last_user_id=user_id,@num:=@num+1,@num:=1) AS seq,(@last_user_id:=user_id) AS tmp FROM (
SELECT user_id,create_date,company_id,gmv
FROM tb_order
GROUP BY user_id ASC,create_date ASC
 
) t1
-- 以下的部分为第二层
) t2 GROUP BY user_id

以上通过聚合函数配合IF有选择的聚合，可以将存在于多行的数据调整到一行上去。

https://blog.csdn.net/hotmoy121/article/details/78262328?fps=1&locationNum=7

##静态转
Select st.stuid, st.stunm, 
    MAX(CASE c.coursenm WHEN '大学语文' THEN s.scores ELSE 0 END ) '大学语文',
    MAX(CASE c.coursenm WHEN '新视野英语' THEN ifnull(s.scores,0) ELSE 0 END ) '新视野英语', 
    MAX(CASE c.coursenm WHEN '离散数学' THEN ifnull(s.scores,0) ELSE 0 END ) '离散数学',
    MAX(CASE c.coursenm WHEN '概率论与数理统计' THEN ifnull(s.scores,0) ELSE 0 END ) '概率论与数理统计',
    MAX(CASE c.coursenm WHEN '线性代数' THEN ifnull(s.scores,0) ELSE 0 END ) '线性代数',
    MAX(CASE c.coursenm WHEN '高等数学(一)' THEN ifnull(s.scores,0) ELSE 0 END ) '高等数学(一)',
    MAX(CASE c.coursenm WHEN '高等数学(二)' THEN ifnull(s.scores,0) ELSE 0 END ) '高等数学(二)'
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

### 动态转 
Select st.stuid, st.stunm, 
(
    SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'MAX(IF(c.coursenm = ''',
          c.coursenm,
          ''', s.scores, NULL)) AS ',
          c.coursenm
        )
      )
    FROM courses c
)
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;

实现方法

MySQL prepare语法：
PREPARE statement_name FROM preparable_SQL_statement; /定义/
EXECUTE statement_name [USING @var_name [, @var_name] ...]; /执行预处理语句/
{DEALLOCATE | DROP} PREPARE statement_name /删除定义/ ;
详解见：http://www.cnblogs.com/gklsy/archive/2012/01/10/2317934.html

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;

proc

DELIMITER &&  
drop procedure if exists SP_QueryData;
Create Procedure SP_QueryData(IN stuid varchar(16))
READS SQL DATA 
BEGIN
 
SET @sql = NULL;
SET @stuid = 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');
                        
IF stuid is not null and stuid <> '' then
SET @stuid = stuid;
SET @sql = CONCAT(@sql, ' Where st.stuid = \'', @stuid, '\'');
END IF;    
 
SET @sql = CONCAT(@sql, ' Group by st.stuid');
 
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
 
END &&  
DELIMITER ;