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 26
| create multiset volatile table mvt_matable, no log
( ID integer
, IDNT_FACT1 integer
, IDNT_FACT2 integer
, IDNT_FACT3 integer
, IDNT_FACT4 integer
)
primary index (ID)
on commit preserve rows;
insert into mvt_matable (ID, IDNT_FACT1, IDNT_FACT2, IDNT_FACT3, IDNT_FACT4) values (1, 5, 4, 9 , 1);
insert into mvt_matable (ID, IDNT_FACT1, IDNT_FACT2, IDNT_FACT3, IDNT_FACT4) values (2, 5, 4, null, 1);
select ID, IDNT_FACT_COL, IDNT_FACT_VAL
from TD_UNPIVOT( on (select ID, IDNT_FACT1, IDNT_FACT2, IDNT_FACT3, IDNT_FACT4
from mvt_matable)
using VALUE_COLUMNS ('IDNT_FACT_VAL')
UNPIVOT_COLUMN('IDNT_FACT_COL')
COLUMN_LIST ('IDNT_FACT1', 'IDNT_FACT2', 'IDNT_FACT3', 'IDNT_FACT4')
) as X
qualify row_number() over(partition by ID order by IDNT_FACT_VAL desc) = 1;
ID IDNT_FACT_COL IDNT_FACT_VAL
-- ------------- -------------
1 IDNT_FACT3 9
2 IDNT_FACT1 5 |