sql查询，列转行

[baidu_18698117]

已知表: assess
员工号（no） 星期号（week） 是否培训（whether）
1 2 有
1 3 有
1 2 有
2 1 有
3 2 有
写一条SQL语句让值变成这样
员工号　星期一　星期二　星期三
　 １　　　　　　　２　　　１　
　２　　　１　　　
３　　　　　　　１

[落落叶叶无声]

WITH assess AS (
SELECT 1 AS NO_, 2 AS WEEK, '有' AS WHETHER FROM DUAL
UNION ALL
SELECT 1 AS NO_, 3 AS WEEK, '有' AS WHETHER FROM DUAL
UNION ALL
SELECT 1 AS NO_, 2 AS WEEK, '有' AS WHETHER FROM DUAL
UNION ALL
SELECT 2 AS NO_, 1 AS WEEK, '有' AS WHETHER FROM DUAL
UNION ALL
SELECT 3 AS NO_, 2 AS WEEK, '有' AS WHETHER FROM DUAL
)
SELECT NO_, 
SUM(DECODE(WEEK, 1, 1, NULL)) 星期一,
SUM(DECODE(WEEK, 2, 1, NULL)) 星期二,
SUM(DECODE(WEEK, 3, 1, NULL)) 星期三 FROM assess
GROUP BY NO_;

[sych888]

case ... when ...

[sych888]

SQL> with tt as( 2 select 1 sno,'2' week from dual union all 3 select 1 ,'3' from dual union all 4 select 1 ,'2' from dual union all 5 select 2 ,'1' from dual union all 6 select 3 ,'2' from dual) 7 select sno,sum(case week when '1' then 1 else 0 end) "星期一" 8 ,sum(case week when '2' then 1 else 0 end) "星期二" 9 ,sum(case week when '3' then 1 else 0 end) "星期三" from tt group by sno; SNO 星期一 星期二 星期三 ---------- ---------- ---------- ---------- 1 0 2 1 2 1 0 0 3 0 1 0

[sych888]

SQL> with tt as( 2 select 1 sno,'2' week from dual union all 3 select 1 ,'3' from dual union all 4 select 1 ,'2' from dual union all 5 select 2 ,'1' from dual union all 6 select 3 ,'2' from dual) 7 select sno,sum(case week when '1' then 1 else null end) "星期一" 8 ,sum(case week when '2' then 1 else null end) "星期二" 9 ,sum(case week when '3' then 1 else null end) "星期三" from tt group by sno; SNO 星期一 星期二 星期三 ---------- ---------- ---------- ---------- 1 2 1 2 1 3 1