sql - varchar 的透视列

Question

我正在尝试对表中的某些数据进行透视，但我无法执行此操作，因为我找不到使用 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/函数，pivot 函数只给我部分输出，对于 count 函数，我得到医生、歌手等的记录数。但我需要实际的行，而不是行数。

我需要的是这个:

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

即假设如果我们有 5 个医生姓名，我们需要在医生列中显示 5 个条目。

Answer 1

我发现使用“row_number()生成的序列号更容易将其表达为条件聚合:

select max(case when occupation = 'Doctor' then name end) as Doctor,
       max(case when occupation = 'Professor' then name end) as Professor,
       max(case when occupation = 'Singer' then name end) as Singer,
       max(case when occupation = 'Actor' then name end) as Actor     
from (select t.*,
             row_number() over (partition by occupation order by name) as seqnum
      from @table t
     ) t
group by seqnum
order by seqnum;