I am working on players database and I have a scenario like below:
Match_Key column has player score data. The value on the right side of = is players score. Like 40, 38, 35 etc. I need to pick the score value and parse into individual Player key column. I wanted to achieve this using regex with case statements. My sample output of SQL code should be look like below. Please help with this:
Match_Key
08=40,02=40,03=38,04=35,05=28,06=36,08=50,02=41, 03=40, 04=90, 05=80, 06=10
Player_Key
08, 02, 03, 04, 05, 06
Player 08 | Player 02 | Player 03 | Player 04 | Player 05 | Player 06
40 | 40 | 38 | 35 | 28 | 36
50 | 41 | 40 | 90 | 80 | 10
Query:
CASE
WHEN Nvl(Player_Key, '') LIKE '%08%' THEN
Regexp_substr(Match_Key, '[^=]*$')
END
AS player 08
The above code works when we have only one key like 02=41 in column, but when there are multiple keys in columns like 02=61,06=51,04=31,01=43, it doesn't work and place one value in all the columns which is wrong. Please help with the regex. Thanks.