表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
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
再對新表交叉表處理。
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.0select 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)
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.0declare @sql varchar(8000)
set @sql='select badge, name, depid,'
select @sql=@sql+quotename(type)+'=sum(case when type='+quotename(type, '''')+' then num end),'
from aot_rest group by typeselect @sql=left(@sql, len(@sql)-1), @sql=@sql+' from aot_rest group by badge, name, depid'
exec(@sql)--result
badge name depid j1 j2 j3
---------- ---------- ---------- ---------------------------------------- ---------------------------------------- ----------------------------------------
000442 xinyue cAO 5.0 3.0 NULL
008466 zhuqiang ca0 14.0 15.0 15.0
name ,
depid ,
isnull( sum(case when type = 'j1' then num end) , 0 ) as j1,
isnull(sum(case when type = 'j2' then num end) , 0 )as j2,
isnull(sum(case when type = 'j3' then num end) , 0 )as j3
from aot_Rest
group by badge ,
name ,
depid