怎样实现下面的SQL语句检索？
假设有以下数据：
公司部门部长姓名
-----------------------------------
AAA01Name1
AAA02Name2
AAA03Name3
BBB01Name4
BBB03Name5
CCC……
已知（公司+部门）为主键，且部门只有01、02、03三种，现需要得到以下报表：
公司部门01的部长姓名部门02的部长姓名部门03的部长姓名
------------------------------------
AAAName1Name2Name3
BBBName4无此部门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
AAAName1Name2Name3
BBBName4NO Name5
CCCName6Name6NO
