如何在SQL Server中使用数据透视表（无聚合）？

一尘不染

如何在SQL Server中使用数据透视表（无聚合）？

sql

请帮助我解决此问题：您将得到一个包含两列的表：column是以下内容之一：

Doctor
Professor
Singer
Actor

编写查询以在相应的occ下输出名称。格式如下：

+--------+-----------+--------+------+

| Doctor | Professor | Singer | Actor|

+--------+-----------+--------+------+

名称必须按字母顺序排列。

样本输入

Name        Occupation
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

笔记

当没有更多与职业相对应的名称时，输出“ NULL”。

我尝试使用：

SELECT *
FROM
(
SELECT [Name], [Occupation] 
FROM occupations 
) AS source
PIVOT
(
    max([Name])
    FOR [occupation] IN ([Doctor], [Professor], [Singer], [Actor]) 
) as pvt;

它给出以下输出：

Priya Priyanka Kristeen Samantha

如何解决？

阅读 149

2021-03-17

共1个答案

一尘不染

您只需要根据名称的职业和字母顺序为它们命名一个行号，然后在您的数据透视查询中包含该行号。

CREATE TABLE Occupations (
     NAME VARCHAR(MAX),
     Occupation VARCHAR(MAX)
    )
INSERT  INTO Occupations
VALUES
        ('Samantha','Doctor'),
        ('Julia','Actor'),
        ('Maria','Actor'),
        ('Meera','Singer'),
        ('Ashley','Professor'),
        ('Ketty','Professor'),
        ('Christeen','Professor'),
        ('Jane','Actor'),
        ('Jenny','Doctor'),
        ('Priya','Singer');

SELECT
    [Doctor],
    [Professor],
    [Singer],
    [Actor]
FROM
    (SELECT 
         ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) rn,
         [Name],
         [Occupation] 
     FROM 
         Occupations
    ) AS source 
PIVOT
    ( MAX([Name]) FOR [occupation] IN ([Doctor],[Professor],[Singer],[Actor]) ) as pvt
ORDER BY rn


DROP TABLE Occupations

2021-03-17