求一种select查詢方法
表aot_rest
badge           name         depid           type         num
008466 zhuqiang ca0 j1 2.0
008466 zhuqiang ca0 j1 3.0
008466 zhuqiang ca0 j2 3.0
008466 zhuqiang ca0 j2 6.0
008466 zhuqiang ca0 j2 6.0
008466 zhuqiang ca0 j3 6.0
008466 zhuqiang ca0 j1 9.0
008466 zhuqiang ca0 j3 9.0
000442 xinyue cAO j1 2.0
000442 xinyue cAO j2 3.0
000442 xinyue cAO j1 3.0
如何select變爲以下的類型
badge           name           depid       j1       j2       j3  
008466     zhuqiang       cao           14.0   15.0     15.0
000442       xinyue         cAO             5.0     3.0

------解决方案--------------------
select badge,name,depid,j1=sum(case when type= 'j1 ' then num end),j2=sum(case when type= 'j2 ' then num end),j3=sum(case when type= 'j3 ' then num end) from aot_rest group by badge,name,depid
------解决方案--------------------
create table aot_rest(badge varchar(10), name varchar(10), depid varchar(10), type varchar(10), num decimal(10,1))
insert aot_rest select '008466 ', 'zhuqiang ', 'ca0 ', 'j1 ', 2.0
union all select '008466 ', 'zhuqiang ', 'ca0 ', 'j1 ', 3.0
union all select '008466 ', 'zhuqiang ', 'ca0 ', 'j2 ', 3.0
union all select '008466 ', 'zhuqiang ', 'ca0 ', 'j2 ', 6.0
union all select '008466 ', 'zhuqiang ', 'ca0 ', 'j2 ', 6.0
union all select '008466 ', 'zhuqiang ', 'ca0 ', 'j3 ', 6.0
union all select '008466 ', 'zhuqiang ', 'ca0 ', 'j1 ', 9.0
union all select '008466 ', 'zhuqiang ', 'ca0 ', 'j3 ', 9.0
union all select '000442 ', 'xinyue ', 'cAO ', 'j1 ', 2.0
union all select '000442 ', 'xinyue ', 'cAO ', 'j2 ', 3.0
union all select '000442 ', 'xinyue ', 'cAO ', 'j1 ', 3.0

select badge, name, depid,
[j1]=sum(case when type= 'j1 ' then num end),
[j2]=sum(case when type= 'j2 ' then num end),
[j3]=sum(case when type= 'j3 ' then num end)
from aot_rest
group by badge, name, depid

--result
badge name depid j1 j2 j3
---------- ---------- ---------- ---------------------------------------- ---------------------------------------- ----------------------------------------
000442 xinyue cAO 5.0 3.0 NULL
008466 zhuqiang ca0 14.0 15.0 15.0

(2 row(s) affected)