mysql行转列 实现oracle的pivot

Mysql 行转列操作

有些业务中可能需要行转列操作
在oracle或者sqlserver中会有pivot完成行转列操作
mysql现有版本中没有对应，只能通过一些自定义操作来实现
比如以下语句

select *
  from (select pid, patientid, signscode, signsvalue, source,  opertime
        from compatient_signs) pivot(sum(signsvalue) for signscode in('temperature' temperture, 'pulse' pulse, 'breath' breath,'rsbp' rsbp, 'rdbp' rdbp,'spo2' spo2, 'height' height, 'weight' weight,'bmi' bmi,'fbgdl' fbgdl,'bhead' bhead)) table;

效果如下

通过查询compatient_signs表可得出以下结果(截取部分)

那么我们用mysql来实现一下

select
*
 from (
	select 
	   pid,patientid,  source,  opertime,
	  sum(signsvalue1) temperature,
	  sum(signsvalue2) pulse,
	  sum(signsvalue3) breath,
	  sum(signsvalue4) rsbp,
	  sum(signsvalue5) rdbp,
	  sum(signsvalue6) spo2,
	  sum(signsvalue7) height,
	  sum(signsvalue8) weight,
	  sum(signsvalue9) bmi,
	  sum(signsvalue10) fbgdl,
	  sum(signsvalue11) bhead
	 from (
	 select pid ,patientid, signscode, signsvalue, source,  opertime,
	    CASE WHEN signscode ='temperature'  THEN signsvalue END signsvalue1,
	    CASE WHEN signscode ='pulse' THEN signsvalue END signsvalue2,
	    CASE WHEN signscode ='breath'THEN  signsvalue END signsvalue3,
	    CASE WHEN signscode ='rsbp' THEN signsvalue END signsvalue4,
	    CASE WHEN signscode ='rdbp' THEN signsvalue END signsvalue5,
	    CASE WHEN signscode ='spo2' THEN signsvalue END signsvalue6,
	    CASE WHEN signscode ='height' THEN signsvalue END signsvalue7,
	    CASE WHEN signscode ='weight'THEN  signsvalue END signsvalue8,
	    CASE WHEN signscode ='bmi' THEN signsvalue END signsvalue9,
	    CASE WHEN signscode ='fbgdl' THEN signsvalue END signsvalue10,
	    CASE WHEN signscode ='bhead' THEN signsvalue END signsvalue11
	    from(select pid, patientid, signscode, signsvalue, source,opertime from compatient_signs GROUP BY PATIENTBUSIID, patientid, signscode, signsvalue, source,  opertime) table0
	) table1 
	 group by pid,patientid, source,  opertime
) table2

```得到的显示结果与oracle一致