Man kann das Ganze noch um einige Sub-Selects reduzieren:
Code:
with
kunden(kdnr, knz1, knz2, knz3, knz4, knz5) as (
values
(1, 'AAA', 'BBB', 'CCC', 'DDD', 'EEE'),
(2, 'AAA', 'BBB', 'BBB', 'CCC', 'DDD')
),
unpivot as (
select distinct
kdnr, knz, dense_rank() over(partition by kdnr order by knz) as knznr
from
kunden
cross join lateral(
values (knz1), (knz2), (knz3), (knz4), (knz5)
) as dt(knz)
)
select KDNR, Coalesce(Min(Case When knzNr = 1 Then KNZ End), '') as KNZ1,
Coalesce(Min(Case When knzNr = 2 Then KNZ End), '') as KNZ2,
Coalesce(Min(Case When KnzNr = 3 Then KNZ End), '') as KNZ3,
Coalesce(Min(Case When KnzNr = 4 Then KNZ End), '') as KNZ4,
Coalesce(Min(Case When KnzNr = 5 Then KNZ End), '') as KNZ5
From Unpivot
Group by KdNr;
Bookmarks