sql - SQL Server 查询侧重于“透视”包含非数字数据的表

Question

表名称：职业

**Name  Occupation**  
Samantha    Doctor    
Julia   Actor
Maria   Actor
Meera   Singer
Ashely  Professor
Ketty   Professor
Christeen   Professor
Jane    Actor
Jenny   Doctor
Priya   Singer

---

目标是编写一个查询来“透视”上述表数据，以便显示以下结果：

Jenny    Ashley     Meera  Jane
Samantha Christeen  Priya  Julia
NULL     Ketty      NULL   Maria

我写了以下查询：

 WITH pivot_data AS
(
SELECT Occupation as Occupation1,  -- Grouping Column
Occupation, -- Spreading Column
Name -- Aggregate Column
FROM Occupations
)
SELECT  [Doctor], [Professor], [Singer], [Actor]
FROM pivot_data 
PIVOT (max(Name) 
       FOR Occupation IN ([Doctor], [Professor], [Singer], [Actor])
                                                            ) AS p;

不幸的是，上述查询给出了以下不正确的结果：

Doctor  Professor   Singer  Actor    
NULL    NULL    NULL    Maria    
Samantha    NULL    NULL    NULL    
NULL    Ketty   NULL    NULL    
NULL    NULL    Priya   NULL

有人可以发布一个将显示所需结果的 sql 查询吗？（另外，如果您可以使用 sql server“pivot”命令执行一个查询，并使用Out sql server“pivot”命令执行另一个查询，那将非常有帮助）

标签： sqlsql-serverpivotaggregateaggregate-functions

Answer 1

我不会为此使用枢轴。条件聚合更易于编写和理解。它的性能也几乎总是稍好一些。

首先，我必须将这些数据转化为可消费的东西。这就是您将来应该如何发布此类信息的方式。

declare @Something table
(
    Name varchar(20)
    , Occupation varchar(20)
)

insert @Something values
('Samantha', 'Doctor')
, ('Julia', 'Actor')
, ('Maria', 'Actor')
, ('Meera', 'Singer')
, ('Ashely', 'Professor')
, ('Ketty', 'Professor')
, ('Christeen', 'Professor')
, ('Jane', 'Actor')
, ('Jenny', 'Doctor')
, ('Priya', 'Singer')
;

现在我们可以使用这些数据很容易地生成您的输出。

with NumberedRows as
(
    select * 
        , RowNum = ROW_NUMBER() over(partition by Occupation order by name)
    from @Something
)

select Doctor = max(case when nr.Occupation = 'Doctor' then nr.Name end)
    , Professor = max(case when nr.Occupation = 'Professor' then nr.Name end)
    , Singer = max(case when nr.Occupation = 'Singer' then nr.Name end)
    , Actor = max(case when nr.Occupation = 'Actor' then nr.Name end)
from NumberedRows nr
group by nr.RowNum