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