create table A(ID int, ListID nvarchar(10), ListName nvarchar(10))
insert A select 1, 'TL1', '方案1'
union all select 2, 'TL2', '报告'
create table B(ID int, PK_ListID nvarchar(10), TypeID int, [Date] datetime)
insert B select 1, 'TL1', 1, '2006-1-1'
union all select 2, 'TL1', 2, '2006-1-2'
union all select 3, 'TL1', 3, '2006-1-3'
union all select 4, 'TL2', 1, '2006-2-1'
union all select 5, 'TL2', 2, '2006-2-2'
select A.ListID, A.ListName,
填写日期=max(case when TypeID=1 then [Date] end),
审核日期=max(case when TypeID=2 then [Date] end),
审批日期=max(case when TypeID=3 then [Date] end)
from B
right join A on A.ListID=B.PK_ListID
group by A.ListID, A.ListName
order by A.ListID
--result
ListID ListName 填写日期 审核日期 审批日期
---------- ---------- ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------
TL1 方案1 2006-01-01 00:00:00.000 2006-01-02 00:00:00.000 2006-01-03 00:00:00.000
TL2 报告 2006-02-01 00:00:00.000 2006-02-02 00:00:00.000 NULL
(2 row(s) affected)