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/function函数，pivot函数只给我部分输出结果，而对于count函数，我只能得到医生、歌手等职业的记录数，但我需要实际的数据行，而不是行数统计。

我所需要的是：

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

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

- MAX

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;

- Gordon Linoff

---

谢谢！但是Order by在其他记录中没有按升序排序，实际上我需要按升序排序所有列，所以我给出了order by 1,2,3,4。 - MAX

@max . . . 如果您想按升序排列名称，则在 row_number() 的 order by 子句中使用 order by name。 - Gordon Linoff

我觉得我们可以用row_number()来实现这个，事实上我已经单独编写了子查询。但是我不知道如何将外部和实际列的两个case语句与内部查询的rowid合并。 - MAX

抱歉，我试图在外部查询中使用“order by”。 - MAX

大家好，有人能解释一下GROUP BY在外部查询中实际上是做什么的吗？这是我的理解。 内部查询将给我基础表中的实际列，并在序列号字段中给出1,2的示例。例如，对于Doctor，我们有两条记录，因此我们得到1,2作为序列号，而外部查询则给我非规范化的列。是的，我知道GROUP BY将对结果集进行分组，即我们消除重复记录。例如，如果我们有一个医生的A DOCTOR 2项条目，只会显示一项。 - MAX

@max...你似乎理解了group by的作用。内部查询计算每个名称在“列列表”中的位置。然后，外部查询根据位置创建这些列表。 - Gordon Linoff

Answer 2

您可以按照您提出的建议使用 PIVOT，只需添加带有 ROW_NUMBER 的列即可：

SELECT [Doctor],[Professor],[Singer],[Actor]
FROM (SELECT name, occupation,
             rn = ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY occupation) 
      FROM @table ) AS src
PIVOT (
  MIN(name)
  FOR occupation IN ([Doctor],[Professor],[Singer],[Actor])
) AS pvt

实时演示

输出：

╔════════╦═══════════╦════════╦═══════╗
║ Doctor ║ Professor ║ Singer ║ Actor ║
╠════════╬═══════════╬════════╬═══════╣
║ A      ║ A         ║ A      ║ A     ║
║ B      ║           ║        ║       ║
╚════════╩═══════════╩════════╩═══════╝

编辑：

您没有说明如何处理更多的行，请考虑这种情况。 上面的解决方案将返回：

╔════════╦═══════════╦════════╦═══════╗
║ Doctor ║ Professor ║ Singer ║ Actor ║
╠════════╬═══════════╬════════╬═══════╣
║ A      ║ A         ║ A      ║ A     ║
║ B      ║           ║ C      ║       ║
╚════════╩═══════════╩════════╩═══════╝

vs:

╔════════╦═══════════╦════════╦═══════╗
║ Doctor ║ Professor ║ Singer ║ Actor ║
╠════════╬═══════════╬════════╬═══════╣
║ A      ║ A         ║ A      ║ A     ║
║ B      ║           ║        ║       ║
║        ║           ║ C      ║       ║
╚════════╩═══════════╩════════╩═══════╝

如果您想使用第二种情况，请使用：

SELECT [Doctor],[Professor],[Singer],[Actor]
FROM (SELECT name, occupation,
             rn = DENSE_RANK() OVER (ORDER BY Name) 
      FROM @table ) AS src
PIVOT (
  MIN(name)
  FOR occupation IN ([Doctor],[Professor],[Singer],[Actor])
) AS pvt

LiveDemo2

这句话涉及编程相关内容，保留了其中的HTML标记和代码格式，并使用了模板语言的语法。

- Lukasz Szozda

---

嗨@lad2025，好帖子。实际上我需要所有列按升序排序。 - MAX

谢谢@lad2025，抱歉我试图在外部查询中使用order by。 - MAX