select empid, sum(case when nID ='d1' then nCount else 0 end) [d1], sum(case when nID ='d2' then nCount else 0 end) [d2], sum(case when nID ='d3' then nCount else 0 end) [d#] from table1 group by empid
select a.empid,d1=(select sum(ncount) from table1 where nid='d1' and empid=a.empid), d2=(select sum(ncount) from table1 where nid='d2' and empid=a.empid), d3=(select sum(ncount) from table1 where nid='d3' and empid=a.empid) from table1 a group by a.empid
if object_id('pubs..tb') is not null drop table tb gocreate table tb ( empid varchar(10), nID varchar(10), nCount int )insert into tb(empid,nID,nCount) values('1001', 'd1', 2) insert into tb(empid,nID,nCount) values('1001', 'd2', 1) insert into tb(empid,nID,nCount) values('1001', 'd3', 1) insert into tb(empid,nID,nCount) values('1002', 'd2', 2)select empid, sum(case when nid = 'd1' then ncount else 0 end) as d1, sum(case when nid = 'd2' then ncount else 0 end) as d2, sum(case when nid = 'd3' then ncount else 0 end) as d3 from tb group by empiddrop table tb/* empid d1 d2 d3 ---------- ----------- ----------- ----------- 1001 2 1 1 1002 0 2 0(所影响的行数为 2 行)*/
sum(case when nID ='d1' then nCount else 0 end) [d1],
sum(case when nID ='d2' then nCount else 0 end) [d2],
sum(case when nID ='d3' then nCount else 0 end) [d#]
from table1
group by empid
d2=(select sum(ncount) from table1 where nid='d2' and empid=a.empid),
d3=(select sum(ncount) from table1 where nid='d3' and empid=a.empid)
from table1 a group by a.empid
drop table tb
gocreate table tb
(
empid varchar(10),
nID varchar(10),
nCount int
)insert into tb(empid,nID,nCount) values('1001', 'd1', 2)
insert into tb(empid,nID,nCount) values('1001', 'd2', 1)
insert into tb(empid,nID,nCount) values('1001', 'd3', 1)
insert into tb(empid,nID,nCount) values('1002', 'd2', 2)select empid,
sum(case when nid = 'd1' then ncount else 0 end) as d1,
sum(case when nid = 'd2' then ncount else 0 end) as d2,
sum(case when nid = 'd3' then ncount else 0 end) as d3
from tb
group by empiddrop table tb/*
empid d1 d2 d3
---------- ----------- ----------- -----------
1001 2 1 1
1002 0 2 0(所影响的行数为 2 行)*/