----------------------------
-- Author  :TravyLee(物是人非事事休，欲语泪先流！)
-- Date    :2012-09-12 14:18:03
-- Version:
--      Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) 
--    Oct 14 2005 00:33:37 
--    Copyright (c) 1988-2005 Microsoft Corporation
--    Developer Edition on Windows NT 6.1 (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
go 
create table [test](组编码 varchar(6),个人编码 varchar(8),电话 varchar(4),级别 varchar(6))
insert [test]
select '100','1',null,'主管' union all
select '100','2','123','操作员' union all
select '101','3',null,'主管' union all
select '101','4','123','操作员' union all
select '101','5','123','操作员'
go

select
    组编码,
    sum(case when 电话 is null or 电话='' then 1 else 0 end) as 无电话,
    sum(case when 电话 is null or 电话='' then 0 else 1 end) as 有电话
from
    test
group by
    组编码
/*
组编码 无电话 有电话
------------------------------
100    1    1
101    1    2
*/

select 
    组编码,
    个人编码,
    case when 电话 is null and 级别='主管' 
        then (select top 1 电话 from test b where a.组编码=b.组编码 and b.级别<>'主管' order by newid()) else 电话 end as 电话,
    
    级别
from
    test a

/*
组编码 个人编码 电话 级别
------------------------------
100    1    123    主管
100    2    123    操作员
101    3    123    主管
101    4    123    操作员
101    5    123    操作员
*/