SQL show a column based on another table's column values

Question

ORACLE SQL with my semi-beginner's experience.

I have table Credits one row per ID:

ID, ArtCred, BizCred, HumCred, NatCred, TekCred, GenCred
5001, 12, 7.5, 12, 14, 11, 9
5002, 10.5, 6, 5, 4, 6, 3

with an ID for each student and the credits for each course group.

Students can select a course group(s) to concentrate on from the GrpChoice table which shows their choice and the date when they made it.

ID, GroupChoice, DateChoice
5001, ART, 3/3/2010
5001, BIZ, 5/5/2015
5001, NAT, 6/23/2022
5002, ART, 6/23/2023

I can query every group's credits for each student even if not chosen):

SELECT
    ID,
    GroupChoice, 
    artcred,
    bizcred,
    humcred,
    natcred,
    tekcred,
    gencred
FROM 
    Grpchoice
INNER JOIN 
    Credits ON GrpChoice.ID = Credits.ID
ORDER BY 
    Grpchoice.datechoice Desc

ID   Group   Art     Biz     Hum     Nat    Tek     Gen
5001  TEK   12  7.5 12  14  11  9
5001  HHS   12  7.5 12  14  11  9
5001  BIZ   12  7.5 12  14  11  9
5001  ART   12  7.5 12  14  11  9
5002  ART       10.5     6       5       4       6      3 

but I need to show for each ID and the credits achieved for the GrpChoice (sort by Date desc):

I want to see only for ID 5001:

 ART 12
 BIZ 7.5
 NAT 14

For ID 5002:

 ART 10.5

I've tried a PIVOT but since the GrpChoice value can change, I don't think I can use that. Unless I set up my data differently to pivot on the ID... I have only read about pivots.

CASE GRPCHOICE won't work because it won't modify the SELECT based on its value (I can't "SELECT CASE When GrpChoice = "Art" then SELECT artCHr, WHEN GrpChoice="BIZ" then SELECT bizCHr...").

I've looked at OVER (PARTITION BY) but I'm not seeing how that would work.

Suggestions are welcome, thanks.

Answer 1

Actuaaly, with sample data provided, you could use CASE expression to get your expected result:

WITH        --  Sample Data
    credits AS
        (
            Select 5001 "ID", 12 "ARTCRED", 7.5 "BIZCRED", 12 "HUMCRED", 14 "NATCRED", 11 "TEKCRED", 9 "GENCRED" From Dual Union All
            Select 5002 "ID", 10.5 "ARTCRED", 6 "BIZCRED",  5 "HUMCRED",  4 "NATCRED",  6 "TEKCRED", 3 "GENCRED" From Dual 
        ),
    grp_choices AS
        (
            Select 5001 "ID", 'ART' "GROUPCHOICE", To_Date('03.03.2010', 'dd.mm.yyyy') "DATECHOICE" From Dual Union All
            Select 5001 "ID", 'BIZ' "GROUPCHOICE", To_Date('05.05.2015', 'dd.mm.yyyy') "DATECHOICE" From Dual Union All
            Select 5001 "ID", 'NAT' "GROUPCHOICE", To_Date('23.06.2022', 'dd.mm.yyyy') "DATECHOICE" From Dual Union All
            Select 5002 "ID", 'ART' "GROUPCHOICE", To_Date('23.06.2023', 'dd.mm.yyyy') "DATECHOICE" From Dual
        )
--
--  M a i n   S Q L
Select  c.ID, 
        g.GROUPCHOICE, 
        CASE g.GROUPCHOICE
            WHEN 'ART' THEN c.ARTCRED
            WHEN 'BIZ' THEN c.BIZCRED
            WHEN 'HUM' THEN c.HUMCRED
            WHEN 'NAT' THEN c.NATCRED
            WHEN 'TEK' THEN c.TEKCRED
            WHEN 'GEN' THEN c.GENCRED
        ELSE 0
        END "CREDIT"
From    credits c
Inner Join grp_choices g ON(g.ID = c.ID)
--
--  R e s u l t :
        ID GROUP     CREDIT
---------- ----- ----------
      5001 ART           12
      5001 BIZ          7.5
      5001 NAT           14
      5002 ART         10.5

Answer 2

Use UNPIVOT, rather than PIVOT, and then join the two tables:

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

Which, for the sample data:

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;

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;

Outputs:

ID	SUBJECT	CREDIT
5001	ART	12
5001	BIZ	7.5
5001	NAT	14
5002	ART	10.5

fiddle