表A
id ygbh zw jb sj
2 10 经理 事假 2
3 10 经理 病假 0
4 10 经理 公休假 2
5 10 经理 出勤 20
6 11 文秘 事假 0
7 11 文秘 病假 1
8 11 文秘 公休假 0
9 11 文秘 出勤 20
......
需查询出下列结果
id ygbh zw 事假 病假 公休假 出勤
2 10 经理 2 0 2 20
3 11 文秘 0 1 0 20
.....
求查询语句怎么写
id ygbh zw jb sj
2 10 经理 事假 2
3 10 经理 病假 0
4 10 经理 公休假 2
5 10 经理 出勤 20
6 11 文秘 事假 0
7 11 文秘 病假 1
8 11 文秘 公休假 0
9 11 文秘 出勤 20
......
需查询出下列结果
id ygbh zw 事假 病假 公休假 出勤
2 10 经理 2 0 2 20
3 11 文秘 0 1 0 20
.....
求查询语句怎么写
事假=sum(case jb when '事假' then jb end),
病假=sum(case jb when '病假' then jb end),
公休假=sum(case jb when '公休假' then jb end),
出勤=sum(case jb when '出勤' then jb end)
from 表a
group by ygbh,zw
insert into t1
select 2,10,'经理','事假',2
union all
select 3,10,'经理','病假',0
union all
select 4,10,'经理','公休假',2
union all
select 5,10,'经理','出勤',20
union all
select 6,11,'文秘','事假',0
union all
select 7,11,'文秘','病假',1
union all
select 8,11,'文秘','公休假',0
union all
select 9,11,'文秘','出勤',20
select * from t1
declare @sql varchar(8000)
set @sql='select min(id) as id,ygbh,zw'
select @sql=@sql+',['+jb+']=sum(case jb when '''+jb+''' then sj else 0 end)' from t1 group by jb
exec(@sql+' from t1 group by ygbh,zw')
drop table t1
create table t(P varchar(5),W varchar(5),A varchar(5),B varchar(5),C varchar(5))
go
insert into t select 'P1','W1', 'a','b','c'
union select 'P1','W2','e','f','g'
union select 'P2','W1','I','j','k'
union select 'P2','W2','m','n','o'
go
select * from t
go
declare @Sql varchar(8000)
set @sql = 'select H.p '
select @sql = @sql +',max(case w when '''+w +''' then A else '''' end) ['+w+'-A]'+char(13)+
',max(case w when '''+w +''' then B else '''' end) ['+w+'-B]'+char(13)+
',max(case w when '''+w +''' then C else '''' end) ['+w+'-C]'+char(13)
from (select distinct w from t ) H
select @sql= @sql + ' from t H group by H.p '
print @sql
exec(@sql)
go
declare @Sql varchar(8000)
set @sql = 'select H.p '
select @sql = @sql +
',max(case w when '''+w +''' then A else '''' end) ['+w+'-A]'+char(13)+
',max(case w when '''+w +''' then B else '''' end) ['+w+'-B]'+char(13)+
',max(case w when '''+w +''' then C else '''' end) ['+w+'-C]'+char(13)
from (select distinct w from t ) H
select @sql= @sql + ' from t H group by H.p '
print @sql
exec(@sql)
select @sql = @sql + ',' + 'max(case jb when ''' + jb + ' '' then sj end) as ' + jb
from t1
group by jbprint @sqlselect @sql = ' select ygbh ,zw ' + @sql + ' from t1 group by ygbh ,zw'print @sql exec (@sql)
事假=sum(case jb when '事假' then jb end),
病假=sum(case jb when '病假' then jb end),
公休假=sum(case jb when '公休假' then jb end),
出勤=sum(case jb when '出勤' then jb end)
from 表a
group by ygbh,zw