varchar的Pivot列

我试图在表格中隐藏一些数据,但我不能这样做,因为我找不到使用varchar列的方法.我有这张桌子:

declare @table  table(name VARCHAR(50) not null, occupation VARCHAR(MAX))

insert into @table values ('A','Doctor')
insert into @table values ('B','Doctor')
insert into @table values ('A','Professor')
insert into @table values ('A','Singer')
insert into @table values ('A','Actor')

SELECT 

CASE WHEN occupation = 'Doctor' THEN NAME END AS Doctor,
CASE WHEN occupation = 'Professor' THEN NAME END AS Professor,
CASE WHEN occupation = 'Singer' THEN NAME END AS Singer,
CASE WHEN occupation = 'Actor' THEN NAME END AS Actor
FROM @table

输出:

Doctor  Professor   Singer  Actor
A   NULL    NULL    NULL
B   NULL    NULL    NULL
NULL    A   NULL    NULL
NULL    NULL    A   NULL
NULL    NULL    NULL    A

而对于Pivot我得到这个输出:

select * from 
(
select name, occupation from @table ) src
pivot (
min(name)
for occupation in ([Doctor],[Professor],[Singer],[Actor])) as pvt

Doctor  Professor   Singer  Actor
A        A          A       A   

而对于min/max/function,pivot函数只给我部分输出,对于count函数,我得到了医生,歌手等的记录数.但是我需要实际的行,而不是行数.

我需要的是这个:

Doctor  Professor   Singer  Actor
A        A          A       A   
B       NULL        NULL    NULL

即假设我们有5个名字给医生,我们需要显示5个医生栏目.