Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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')
- 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
- Code Level1 Level2 Level3 Level4 Level5
- ---- ----------- ----------- ----------- ----------- -----------
- A A10 A9 A8 A7 A6
- B B6 B5 B4 B3 B2
- C C3 C2 C1 NULL NULL
- 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 )
- ;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;
- Code Level1 Level2 Level3 Level4 Level5
- ---- ----------- ----------- ----------- ----------- -----------
- A A10 A9 A8 A7 A6
- B B6 B5 B4 B3 B2
- C C3 C2 C1 NULL NULL
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement