怎样实现下面的SQL语句检索？
假设有以下数据：
公司 部门 部长姓名
-----------------------------------
AAA 01 Name1
AAA 02 Name2
AAA 03 Name3
BBB 01 Name4
BBB 03 Name5
CCC … …
已知（公司+部门）为主键，且部门只有01、02、03三种，现需要得到以下报表：
公司 部门01的部长姓名 部门02的部长姓名 部门03的部长姓名
------------------------------------
AAA Name1 Name2 Name3
BBB Name4 无此部门 Name5
CCC …
        请试用一个SQL语句完成以上数据的检索。


------解决方案--------------------
create table t1(id int,score int,number int)
insert into t1 select 1,90,1
insert into t1 select 1,60,2
insert into t1 select 1,50,3
insert into t1 select 1,85,4

insert into t1 select 2,91,1
insert into t1 select 2,61,2
insert into t1 select 2,81,4

insert into t1 select 3,92,1
insert into t1 select 3,62,2
insert into t1 select 3,52,3

create table t(id int,title nvarchar(30))
insert into t select 1, '你是 '

insert into t select 2, '我不是 '

insert into t select 3, '我是好人 '

Select a.id,b.title,
max(case a.Number when 1 then (a.score) else 0 end) as Number1,
max(case a.Number when 2 then (a.score) else 0 end) as Number2,
max(case a.Number when 3 then (a.score) else 0 end) as Number3,
max(case a.Number when 4 then (a.score) else 0 end) as Number4
From
t1 as a,t as b
where a.id = b.id
group by a.id,b.title

drop table t1
drop table t
------解决方案--------------------

declare @t table(company varchar(20),dept_id varchar(20),deptmen varchar(20))
insert into @t select 'AAA ', '01 ', 'Name1 '
insert into @t select 'AAA ', '02 ', 'Name2 '
insert into @t select 'AAA ', '03 ', 'Name3 '
insert into @t select 'BBB ', '01 ', 'Name4 '
insert into @t select 'BBB ', '03 ', 'Name5 '

select company,max(case when dept_id= '01 ' then deptmen else null end) as '部门01部长姓名 ',
max(case when dept_id= '02 ' then deptmen else null end) as '部门02部长姓名 ',
max(case when dept_id= '03 ' then deptmen else null end)as '部门03部长姓名 '
from @t
group by company
------解决方案--------------------

CREATE TABLE TABLEA
(
CompanyY VARCHAR(10),
depart VARCHAR(10),
departName VARCHAR(10)
)
INSERT INTO TABLEA SELECT 'AAA ' , '01 ' , 'Name1 ' union all select
'AAA ', '02 ' , 'Name2 ' union all select
'AAA ' , '03 ' , 'Name3 ' union all select
'BBB ' , '01 ' , 'Name4 ' union all select
'BBB ' , '03 ' , 'Name5 ' union all select
'CCC ' , '02 ', 'Name6 ' union all select
'CCC ' , '01 ' , 'Name6 '

Select CompanyY,
MIN(CASE depart WHEN '01 ' THEN departName ELSE 'NO ' END) AS '01DEPART ',
MIN(CASE depart WHEN '02 ' THEN departName ELSE 'NO ' END) AS '02DEPART ',
MIN(CASE depart WHEN '03 ' THEN departName ELSE 'NO ' END) AS '03DEPART '
From
TABLEA group by CompanyY

CompanyY 01DEPART 02DEPART 03DEPART
AAA Name1 Name2 Name3
BBB Name4 NO Name5
CCC Name6 Name6 NO