Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE credits (ID, ArtCred, BizCred, HumCred, NatCred, TekCred, GenCred) AS
SELECT 5001, 12, 7.5, 12, 14, 11, 9 FROM DUAL UNION ALL
SELECT 5002, 10.5, 6, 5, 4, 6, 3 FROM DUAL;
2 rows affected

CREATE TABLE GrpChoice (ID, GroupChoice, DateChoice) AS
SELECT 5001, 'ART', DATE '2010-03-03' FROM DUAL UNION ALL
SELECT 5001, 'BIZ', DATE '2015-05-05' FROM DUAL UNION ALL
SELECT 5001, 'NAT', DATE '2022-06-23' FROM DUAL UNION ALL
SELECT 5002, 'ART', DATE '2023-06-23' FROM DUAL;
4 rows affected

SELECT c.id, subject, credit
FROM   credits
       UNPIVOT (
         credit FOR subject IN (
           artcred As 'ART',
           bizcred AS 'BIZ',
           humcred AS 'HUM',
           natcred AS 'NAT',
           tekcred AS 'TEK',
           gencred AS 'GEN'
         )
       ) c
       INNER JOIN grpchoice g
       ON c.id = g.id AND c.subject = g.groupchoice


ID
SUBJECT
CREDIT


5001
ART
12

5001
BIZ
7.5

5001
NAT
14

5002
ART
10.5