1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
|
;with cte as
(select S06LFN,
S06LFX,
S06NAN,
S06VON,
S06Woo,
S06FK1,
S06US1,
S06KAP,
row_number() over (PARTITION BY S06LFN ORDER BY s06fk1 desc ) AS 'RowNum'
FROM DATABASE_NAME)
SELECT distinct
t1.S06LFN
, t1.S06NAN SH_PRS1NAM ,t1.S06VON SH_PRS1VNA, t1.S06Woo SH_PRS1WOO, t1.S06FK1 SH_PRSFKT1, t1.S06US1 SH_PRS1USC, t1.S06KAP SH_PRS1KAP
, t2.S06NAN SH_PRS2NAM ,t2.S06VON SH_PRS2VNA, t2.S06Woo SH_PRS2WOO, t2.S06FK1 SH_PRSFKT2, t2.S06US1 SH_PRS2USC, t2.S06KAP SH_PRS2KAP
, t3.S06NAN SH_PRS3NAM ,t3.S06VON SH_PRS3VNA, t1.S06Woo SH_PRS1WOO, t3.S06FK1 SH_PRSFKT3, t3.S06US1 SH_PRS3USC, t3.S06KAP SH_PRS3KAP
, t4.S06NAN SH_PRS4NAM ,t4.S06VON SH_PRS4VNA, t1.S06Woo SH_PRS1WOO, t4.S06FK1 SH_PRSFKT4, t4.S06US1 SH_PRS4USC, t4.S06KAP SH_PRS4KAP
, t5.S06NAN SH_PRS5NAM ,t5.S06VON SH_PRS5VNA, t1.S06Woo SH_PRS1WOO, t5.S06FK1 SH_PRSFKT5, t5.S06US1 SH_PRS5USC, t5.S06KAP SH_PRS5KAP
from cte t1
left join cte t2 on t1.S06LFN = t2.S06LFN and t2.RowNum = 2
left join cte t3 on t1.S06LFN = t3.S06LFN and t3.RowNum = 3
left join cte t4 on t1.S06LFN = t4.S06LFN and t4.RowNum = 4
left join cte t5 on t1.S06LFN = t5.S06LFN and t3.RowNum = 5
where t1.RowNum = 1 |