DB2 11.1 - Unpivot table and choose value based on condition

Question

I'm working in DB2 v11.1

I have a table in the following format:

CREATE TABLE SCHEMA.TEST_TABLE (
NAME VARCHAR(5)
,RATING VARCHAR(4)
,AAA_X INTEGER
,AAA_Y INTEGER
,BBB_X INTEGER
,BBB_Y INTEGER
,CCC_X INTEGER
,CCC_Y INTEGER
,DDD_X INTEGER
,DDD_Y INTEGER
)

INSERT INTO SCHEMA.TEST_TABLE (
  NAME, RATING, AAA_X, AAA_Y, BBB_X, BBB_Y, CCC_X, CCC_Y,DDD_X,DDD_Y)
VALUES
('a','AAA',10,0,20,10,15,20,30,40),
('b','BBB',20,5,40,10,20,10,10,20),
('c','CCC',30,15,50,10,5,12,30,40)

Let's call name as TRADE for simplicity. Rating order: AAA > BBB > CCC > DDD and so on. I need to thell _X and _Y in case the rating would drop by 2 for each trade.

Based on the table above for trade 'a', if the rating would drop 2 (AAA > CCC) then the CCC_X and CCC_Y should be picked and shown in the following format. If it were to drop 3 then (AAA > DDD) and DDD_X and DDD_Y should be picked. For 'b' trade, a 2 rating drop would mean BBB > DDD.

If it would go below the last available rating (DDD in this example) then pick the last available.

name | 2_drop_X | 2_drop_Y | 3_drop_X | 3_drop_Y
a        15         20         30         40
b        10         20         10         20
c        30         40         30         40

So far I've got the following:

SELECT
   ID, RATE, VALUE, TYPE
FROM SCHEMA.TEST_TABLE
   LATERAL(VALUES
    (P.NAME, P.RATING, AAA_X,'AAA_X'),
    (P.NAME, P.RATING, AAA_Y,'AAA_Y'),
    (P.NAME, P.RATING, BBB_X,'BBB_X'),
    (P.NAME, P.RATING, BBB_Y,'BBB_Y'),
    (P.NAME, P.RATING, CCC_X,'CCC_X'),
    (P.NAME, P.RATING, CCC_Y,'CCC_Y'),
    (P.NAME, P.RATING, DDD_X,'DDD_X'),
    (P.NAME, P.RATING, DDD_Y,'DDD_Y')
) AS T(ID,RATE,VALUE,TYPE)

The distance between the rating won't change, meaning AAA will be always 2 positions away from CCC. I was thinking to create a mapping table a do a join based on that, but I'm stuck.

Mapping table in subject:

CREATE TABLE SCHEMA.TEST_MAPPING
(
TYPE VARCHAR(10)
,RATING VARCHAR(4)
,X_POS INTEGER
,Y_POS INTEGER
)

INSERT INTO SCHEMA.TEST_MAPPING
('AAA_X','AAA',1,0),
('AAA_Y','AAA',0,1),
('BBB_X','BBB',2,0),
('BBB_Y','BBB',0,2),
('CCC_X','CCC',3,0),
('CCC_Y','CCC',0,3),
('DDD_X','DDD',4,0),
('DDD_Y','DDD',0,4)

Am I going in the wrong direction? Thanks in advance

Edit: grammar

Can't you use a `CASE` clause to differentiate the cases? – The Impaler Jan 22 '20 at 16:45 — The Impaler, Jan 22 '20 at 16:45

score 1 · Answer 1 · answered Jan 22 '20 at 16:50

Unless I'm got it wrong, the query to get that result should be pretty simple:

select
  name,
  case when rating = 'AAA' then ccc_x else ddd_x end as "2_drop_X",
  case when rating = 'AAA' then ccc_y else ddd_y end as "2_drop_Y",
  ddd_x as "3_drop_X",
  ddd_y as "3_drop_Y"
from schema.test_table

Result:

NAME  2_drop_X  2_drop_Y  3_drop_X  3_drop_Y
----  --------  --------  --------  --------
a           15        20        30        40           
b           10        20        10        20           
c           30        40        30        40

Thanks for your comment, and yes this could work. However there are 16 different ratings and I was looking for a more dynamic solution if possible. Meaning the rating change can be between 1-10. Do you have any suggestion in this case? — not Steve, Jan 22 '20 at 16:58

DB2 11.1 - Unpivot table and choose value based on condition

1 Answers1