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:
08=40,02=40,03=38,04=35,05=28,06=36,08=50,02=41, 03=40, 04=90, 05=80, 06=10
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
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.
This question is not yet answered, be the first one who answer using the comment. Later the confirmed answer will be published as the solution.