T-SQL将行转换为列，当试图将最底的5个值转换为列时?

I am using MS SQL Server 2005.

我正在使用MS SQL Server 2005。

I think PIVOT could help me here, but I can't figure it out. I must be over thinking. Here is the input

我认为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')

I can do it with T-SQL when I passing the id. But I seems like there is an easy sql view that I could create that doesn't require me to send it the id. Here is the T-SQL that gets me the output I want for one id:

我可以在传递id时使用T-SQL来实现，但我似乎可以创建一个简单的sql视图，不需要我将id发送给它。

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

Ideally this would be be my output of SQL I am looking for:

理想情况下，这将是我正在寻找的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

2 个解决方案

#1

Since you want only the top 5 levels, you don't need any dynamic pivoting and this should work:

既然你只想要前五层，你不需要任何动态的旋转，这应该是可行的:

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

Update

更新

Now, if you want to make things more interesting and have your levels dynamically, here's the not-so-trivial-yet-lot-of-fun-on-coding solution:

现在，如果你想让事情变得更有趣，让你的层次更动态，这里有一个不是那么简单的编码解决方案:

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 )

#2

Try this solution based on PIVOT operator:

尝试基于主算子的解决方案:

;WITH PivotSource
AS
(
SELECT   a.id
        ,a.seq_i
        ,ROW_NUMBER() OVER(PARTITION BY a.id ORDER by a.seq_i DESC) row_num
FROM    #myrows a
)
SELECT   pvt.id                                 AS Code
        ,pvt.id + CONVERT(VARCHAR(10), pvt.[1]) AS Level1
        ,pvt.id + CONVERT(VARCHAR(10), pvt.[2]) AS Level2
        ,pvt.id + CONVERT(VARCHAR(10), pvt.[3]) AS Level3
        ,pvt.id + CONVERT(VARCHAR(10), pvt.[4]) AS Level4
        ,pvt.id + CONVERT(VARCHAR(10), pvt.[5]) AS Level5                               
FROM    PivotSource src
PIVOT   ( MAX(src.seq_i) FOR src.row_num IN([1], [2], [3], [4], [5]) ) pvt;

Results:

结果:

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