#sql #sql-server #sql-server-2005 #pivot
#sql #sql-server #sql-server-2005 #сводная
Вопрос:
Я использую MS SQL Server 2005.
Я думаю, что PIVOT мог бы мне здесь помочь, но я не могу в этом разобраться. Я, должно быть, слишком много думаю. Вот входные данные
create table #myrows (id char(1), seq_i int, val char(10))
insert into #myrows values('A',1, 'A1')
insert into #myrows values('A',2, 'A2')
insert into #myrows values('A',3, 'A3')
insert into #myrows values('A',4, 'A4')
insert into #myrows values('A',5, 'A5')
insert into #myrows values('A',6, 'A6')
insert into #myrows values('A',7, 'A7')
insert into #myrows values('A',8, 'A8')
insert into #myrows values('A',9, 'A9')
insert into #myrows values('A',10, 'A10')
insert into #myrows values('B',1, 'B1')
insert into #myrows values('B',2, 'B2')
insert into #myrows values('B',3, 'B3')
insert into #myrows values('B',4, 'B4')
insert into #myrows values('B',5, 'B5')
insert into #myrows values('B',6, 'B6')
insert into #myrows values('C',1, 'C1')
insert into #myrows values('C',2, 'C2')
insert into #myrows values('C',3, 'C3')
Я могу сделать это с помощью T-SQL при передаче идентификатора. Но мне кажется, что я мог бы создать простое представление sql, которое не требует от меня отправки идентификатора. Вот T-SQL, который выдает мне желаемый результат для одного идентификатора:
DECLARE @max_hierarchy int
DECLARE @code CHAR(1)
select @code = 'C'
SELECT @max_hierarchy = max(seq_i)
FROM #myrows
WHERE id=@code
SELECT top 1
(SELECT val from #myrows WHERE id=@code AND seq_i = @max_hierarchy) AS 'Level1',
(SELECT val from #myrows WHERE id=@code AND seq_i = @max_hierarchy-1) AS 'Level2',
(SELECT val from #myrows WHERE id=@code AND seq_i = @max_hierarchy-2) AS 'Level3',
(SELECT val from #myrows WHERE id=@code AND seq_i = @max_hierarchy-3) AS 'Level4',
(SELECT val from #myrows WHERE id=@code AND seq_i = @max_hierarchy-4) AS 'Level5'
from #myrows
WHERE id=@code
В идеале это был бы мой вывод SQL, который я ищу:
Code Level1 Level2 Level3 Level4 Level5
---- ----------- ----------- ----------- ----------- -----------
A A10 A9 A8 A7 A6
B B6 B5 B4 B3 B2
C C3 C2 C1 NULL NULL
Комментарии:
1. Такого рода преобразования часто лучше обрабатываются на уровне представления, чем в SQL.
2. @JamieF Вы можете быть правы в большинстве случаев. Однако проверьте мой ответ. Это не так сложно и эффективно. Кодирование, которое в презентации может содержать чуть больше 13 строк кода
3. @Adrian — Справедливо, рекурсия CTE — хороший ответ, но ваш второй бит кода является более распространенным требованием, и, ИМХО, методы SQL для изменения количества полей не так хороши, как, скажем, табликс в SSRS. (Но я все равно дам вашему ответу 1.)
Ответ №1:
Поскольку вам нужны только верхние 5 уровней, вам не нужно никакого динамического поворота, и это должно сработать:
with cte as (
select id, seq_i, val, ROW_NUMBER() over (partition by id order by seq_i desc) rn
from #myrows
)
select
id,
max(case rn when 1 then val else null end) Level1,
max(case rn when 2 then val else null end) Level2,
max(case rn when 3 then val else null end) Level3,
max(case rn when 4 then val else null end) Level4,
max(case rn when 5 then val else null end) Level5
from cte
group by id
Обновить
Теперь, если вы хотите сделать вещи более интересными и динамически изменять свои уровни, вот не столь тривиальное, но очень интересное решение для кодирования:
create table #cte (id char(1), seq_i int, val char(10), level varchar(10))
;with cte as (
select id, seq_i, val, ROW_NUMBER() over (partition by id order by seq_i desc) rn
from #myrows
)
insert into #cte (id, seq_i, val, level)
select id, seq_i, val,
'Level' right('000' cast(rn as varchar), 4) from cte
DECLARE @cols VARCHAR(1000)
DECLARE @sqlquery VARCHAR(2000)
SELECT @cols = STUFF(( SELECT distinct ',' QuoteName(level)
FROM #cte FOR XML PATH('') ), 1, 1, '')
select @cols
SET @sqlquery = 'SELECT * FROM
(SELECT id, level, val
FROM #cte ) base
PIVOT (max(val) FOR [level]
IN (' @cols ')) AS finalpivot'
EXECUTE ( @sqlquery )