问题：如题
A表显示如下：
ID Num State Name
1 10 排产 zhang
2 20 排产 zhang
3 30 发货 zhang
4 40 发货 zhang
5 30 排产 chen
6 20 排产 chen
7 25 发货 zhang
现在我想要的结果是:根据姓名zhang将状态：排产、发货汇总成一条数据进行显示：
ID 排产 发货 Name
1 30 95 zhang
------解决方案--------------------
select a.ID, b.* from
(select [Name], min(ID) as ID from A group by [Name]) a,
(select [Name], sum(case State when '排产' then Num else 0 end) as 排产, sum(case State when '发货' then Num else 0 end) as 发货 from A group by [Name]) b
where a.[Name] = b.[Name];