Mysql 枢轴创建

Question

我的数据库中有一个表；

CREATE TABLE `Comment` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `JobId` int(11) DEFAULT NULL,
  `Description` text,
  PRIMARY KEY (`Id`) )
 ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

这是内容:

Id  JobId   Description
1   1010    Done
2   1010    Success
3   1020    Fail
4   1020    status
5   1030     ..
6   ..      ..

我想让数据透视表像这样动态:

JobId   description1    description2   description3 descriptin4...description[n]
1010    Done            Success           Fail
1020    status          null              null
1030    ..              ..               ..

描述列的数量可能会增加。有什么建议吗？

Answer 1

将条件聚合与 case when 表达式结合使用

<强> demo

select jobid, 
       max(case when description in ('Done','Status') then description end) as description1,
       max(case when description in ('Success') then description end) as description2,
       max(case when description in ('Fail') then description end) as description3
from tablename
group by jobid