Использование PIVOT в SQL

Question

Я изучаю PIVOT в SQL и была такая задача:

Есть таблица

Пример данных

Нужно вывести данные в таком формате:

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

Пояснение: Нужно вывести данные в перевернутом виде со столбцами (Doctor, Professor, Singer, Actor) Имена должны быть в алфавитном порядке.

Ниже код который создает эту таблицу и выводит данные в нужном формате.

Весь код

if object_id('tempdb.dbo.#OCCUPATIONS') is not null
    drop table #OCCUPATIONS



create table #OCCUPATIONS (
    Name nvarchar(1000),
    Occupation nvarchar(1000)
)









insert  into  #OCCUPATIONS(Name,Occupation)
values 
('Ashley'      ,'Professor'     )
,('Samantha'    ,'Actor'         )
,('Julia'       ,'Doctor'        )
,('Britney'     ,'Professor'     )
,('Maria'       ,'Professor'     )
,('Meera'       ,'Professor'     )
,('Priya'       ,'Doctor'        )
,('Priyanka'    ,'Professor'     )
,('Jennifer'    ,'Actor'         )
,('Ketty'       ,'Actor'         )
,('Belvet'      ,'Professor'     )
,('Naomi'      ,'Professor'      )
,('Jane'        ,'Singer'        )
,('Jenny'       ,'Singer'        )
,('Kristeen'    ,'Singer'        )
,('Christeen'   ,'Singer'        )
,('Eve'         ,'Actor'         )
,('Aamina'      ,'Doctor'        )


select [Doctor] as Doctor,
       [Professor] as Professor,
       [Singer] as Singer,
       [Actor] as Actor
from 
(    /* select Name, Occupation from #OCCUPATIONS */ --<-- Моя строчка
    
select ROW_NUMBER() OVER (PARTITION BY OCCUPATION ORDER BY NAME) [RowNumber], *  from #OCCUPATIONS --<-- Строчка которую я нашел поисковике
    
    

) as source_talbe

PIVOT
(
      max (Name ) for Occupation  in ([Doctor],[Professor],[Singer],[Actor] )
     
)as PVT

Вывод:

Что я хочу от вас: Я не понимаю как работает данный код и почему мой код (в коде написан комментарием) не работал. Может кто то сможет объяснить как он работает?

Answer 1

Ваш запрос работает как и должен, он выбирает максимальное имя для каждой должности, соответственно по 1-й на каждую должность.

Если же вы хотите видеть все имена, то нужно вывести какое либо поле, отличное от того что используется в агрегатной функции. Например заведём в исходной таблице поле с признаком IDENTITY и выведем его, тогда получим результат в чистом, не сгруппированом виде:

create table #OCCUPATIONS (
    id INT IDENTITY,
    Name nvarchar(1000),
    Occupation nvarchar(1000)
)

...

SELECT 
    [Doctor] AS Doctor,
    [Professor] AS Professor,
    [Singer] AS Singer,
    [Actor] AS Actor
FROM 
(    
    o.id,
    o.Name, 
    o.Occupation
FROM #OCCUPATIONS o
) AS source_talbe
PIVOT
(
      MAX(NAME) FOR Occupation IN ([Doctor],[Professor],[Singer],[Actor] )     
) AS PVT

Та строчка, которую вы в интернете нашли, позволяет сгенерировать вес имени относительно должности, таким образом строк в результате становится меньше и результат будет более читабельный. Аналогично можно воспользоваться конструкцией RANK() для большей наглядности, но результат будет похожим.

SELECT 
    [Doctor] AS Doctor,
    [Professor] AS Professor,
    [Singer] AS Singer,
    [Actor] AS Actor
FROM 
(    
    SELECT 
        RANK() OVER ( PARTITION BY o.Occupation ORDER BY o.Name ) AS RowRank,
        o.Name, 
        o.Occupation
    FROM #OCCUPATIONS o
) AS source_talbe
PIVOT
(
      MAX(NAME) FOR Occupation IN ([Doctor],[Professor],[Singer],[Actor] )     
) AS PVT

БЛОГ НА HUSL

Использование PIVOT в SQL

Ответы (1 шт):