sql - 在 SQL 中使用 Pivot 检索多行

Question

我很难理解下面用于透视表的 SQL 查询，

表名称:职业

Name    | Occupation
-----------------
Sam     | Actor
Julia   | Singer
Ketty   | Actor

预期结果:

Actor   | Singer
----------------
Sam     | Julia
Ketty   | null

我只得到一行，当我执行以下查询时，

Select [Actor],[Singer] From ( Select Occupation,Name From Occupations
 )sub Pivot (Max(Name) For Occupation in ([Actor],[Singer])) pvt

输出:

Actor   | Singer
----------------
Sam | Julia

当我使用 RowNumber() 修改上述查询时，我得到了预期的结果(多行)。

 Select [Actor],[Singer] 
 From ( Select Occupation,Name,Row_Number() over(partition by Occupation order by Name)SNo
 From Occupations )sub
 Pivot (Max(Name) For Occupation in ([Actor],[Singer])) pvt

你能解释一下，在子查询中添加 Row_Number 函数如何给出多行吗？

Answer 1

Pivot 是一种棘手的语法。它为除数据透视涉及的列之外的所有列组合返回一行。当你这样做时:

Select [Actor], [Singer] 
From (Select Occupation, Name,
      From Occupations
     ) sub
Pivot (Max(Name) For Occupation in ([Actor], [Singer])) pvt

没有其他行。因此，返回一行。

当您添加时:

row_Number() over (partition by Occupation order by Name) as sno

您现在有了另一列不同的列，并且它由数据透视表(隐式)使用。

这种尴尬的行为是我更喜欢条件聚合而不是透视的原因之一。