SQL Server сводные текстовые значения

Я практикую пивот. И у меня есть таблица с данными об имени и профессии (код для создания образца ниже). Мне нужно поместить Профессии в столбцы и расположить под каждым занятием имена. Нуль в порядке. Я видел несколько постов, и это то, что мне нужно, но однажды адаптированный к моим данным, он извлекает только 1 ряд данных.

Вот код для генерации образца и фактический сводный код. Пожалуйста, помогите исправить эту ошибку 

declare @occupations table (ename nvarchar(10), occupation nvarchar(10) )
insert @occupations
values 
('John','Doctor'),('Mary','Doctor'),('Jack','Doctor'),('Anna','Doctor'),
('Jim','Singer'),('Kate','Singer'),('Helen','Actor'),('Paco','Singer')

SELECT [Doctor] Doctor, [Singer] Singer, [Actor] Actor
FROM @occupations
PIVOT
  (min(ename)  
    FOR occupation IN ([Doctor],[Singer], [Actor])  
  )AS pp

Я получаю этот результат: 

 Doctor Singer Actor
 Anna   Jim    Helen

Там должно быть больше строк, чтобы включить все имена. Т.е. вы можете увидеть в заявлении на вкладку, что, например, 4 врача. Так должно быть 8 строк с некоторыми нулями.

Всего 3 ответа

Если вам нужны все строки, просто добавьте уникальный ключ, например row_number ()

пример 

SELECT [Doctor] Doctor, [Singer] Singer, [Actor] Actor
FROM (
       Select *
             ,rn=row_number() over(order by ename) 
        from @occupations
     ) src
PIVOT
  (min(ename)  
    FOR occupation IN ([Doctor],[Singer], [Actor])  
  )AS pp

Возвращает 

Doctor  Singer  Actor
Anna    NULL    NULL
NULL    NULL    Helen
Jack    NULL    NULL
NULL    Jim     NULL
John    NULL    NULL
NULL    Kate    NULL
Mary    NULL    NULL
NULL    Paco    NULL

SQL Fiddle

Настройка схемы MS SQL Server 2017 : 

create table occupations (ename nvarchar(10), occupation nvarchar(10) )
insert into occupations(ename,occupation)values ('John','Doctor'),('Mary','Doctor'),('Jack','Doctor'),('Anna','Doctor'),
('Jim','Singer'),('Kate','Singer'),('Helen','Actor'),('Paco','Singer')

Запрос 1 : 

WITH CTE AS (
  SELECT *,
  (case when occupation like 'singer' then ename end) AS Singer,
    (case when occupation like 'doctor' then ename end) AS Doctor,
    (case when occupation like 'Actor' then ename end) AS Actor,
  ROW_Number() OVER (PARTITION BY occupation Order By ename) as rn
  from occupations
  group by ename,occupation)

  select max(Singer) AS Singer,max(Doctor) AS Doctor, max(Actor) AS Actor
  from cte
  where rn=1

Результаты : 

| Singer | Doctor | Actor |
|--------|--------|-------|
|    Jim |   Anna | Helen |

Вы также можете сделать его динамическим, чтобы автоматически выбрать все столбцы 

create table #occupations  (ename nvarchar(10), occupation nvarchar(10) )
insert   #occupations
values 
('John','Doctor'),('Mary','Doctor'),('Jack','Doctor'),('Anna','Doctor'),
('Jim','Singer'),('Kate','Singer'),('Helen','Actor'),('Paco','Singer') ;


DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(occupation) 
            FROM #occupations 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

set @query = 'SELECT * FROM #occupations
                    PIVOT
                        (min(ename)  
                FOR occupation IN ('+ @cols +')  
              )AS pp'
exec (@query) 
drop table #occupations

Есть идеи?

10000