mysql 行转列 队列

mysql 行转列 行列

表kaoqin是这样的

我想通过行转列得出下面这样的结果

etimattendancelistid 上午上班 下午上班

0030                                   旷工   迟到

0061                                   旷工   旷工

该怎么写,用mysql数据库的

我用

select etimattendancelistid,

sum(case when etimkaoqinfanwei=’上午上班’ then etimkaoqinzhuangtai end) As ‘上午上班’,

sum(case when etimkaoqinfanwei=’下午上班’ then etimkaoqinzhuangtai end) As ‘下午上班’

From  kaoqin  By etimattendancelistid

结果出现这种状况

后面用了

select etimattendancelistid,

concat(case when etimkaoqinfanwei=’上午上班’ then etimkaoqinzhuangtai end) As ‘上午上班’,

concat(case when etimkaoqinfanwei=’下午上班’ then etimkaoqinzhuangtai end) As ‘下午上班’

From  kaoqin  By etimattendancelistid

结果还是不能满足!

请问大家怎么写查询语句?

1 楼 liveHappy 2010-07-21  

要不你用一下

select etimattendancelistid,
max(case when etimkaoqinfanwei='上午上班' then  etimkaoqinzhuangtai  end) as '上午上班', max(case when etimkaoqinfanwei='上午上班' then  etimkaoqinzhuangtai  end) as '下午上班'
From  kaoqin group by etimattendancelistid

2 楼 liveHappy 2010-07-21  

上面的写错了

select etimattendancelistid,
max(case when etimkaoqinfanwei='上午上班' then  etimkaoqinzhuangtai  end) as '上午上班', max(case when etimkaoqinfanwei='上午上班' then  etimkaoqinzhuangtai  end) as '下午上班'
From  kaoqin order by etimattendancelistid