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.
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