原来的格式是:
StaffID Task
1 点火
1 点火
1 换表
1 维修
2 点火
2 维修
3 换表
转化成:
StaffID 点火 换表 维修
1 2 1 1
2 1 0 1
3 0 1 0
请各位大哥帮忙!!!
StaffID Task
1 点火
1 点火
1 换表
1 维修
2 点火
2 维修
3 换表
转化成:
StaffID 点火 换表 维修
1 2 1 1
2 1 0 1
3 0 1 0
请各位大哥帮忙!!!
insert into tbtest
select 1,'点火'
union all select 1,'点火'
union all select 1,'换表'
union all select 1,'维修'
union all select 2,'点火'
union all select 2,'维修'
union all select 3,'换表'select StaffID,
sum(case when Task='点火' then 1 else 0 end) as '点火',
sum(case when Task='换表' then 1 else 0 end) as '换表',
sum(case when Task='维修' then 1 else 0 end) as '维修'
from tbtest
group by StaffID/*
StaffID 点火 换表 维修
----------- ----------- ----------- -----------
1 2 1 1
2 1 0 1
3 0 1 0(所影响的行数为 3 行)
*/
create table tbtest(StaffID int,Task varchar(10))
insert into tbtest
select 1,'点火'
union all select 1,'点火'
union all select 1,'换表'
union all select 1,'维修'
union all select 2,'点火'
union all select 2,'维修'
union all select 3,'换表'declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case when Task='''+Task+''' then 1 else 0 end) as '''+Task+''''
from (select distinct Task from tbtest)t
exec('select Staffid'+@sql+' from tbtest group by Staffid')/*
Staffid 点火 换表 维修
----------- ----------- ----------- -----------
1 2 1 1
2 1 0 1
3 0 1 0
*/
哎好久没有写SQL 语句了能力退化了啊