CODEWITHSUNDEEP
sqloracle-databaseoracle11ggroup-bynull
Brian Brock
Brian Brock

Reputation: 351

Merge multiple records in single row

I am trying to get two separate records to display on a single line, but keep getting multiple lines with nulls. I thought that if I grouped by Test_Date, I'd be able to get a single row for each test date, with the scores on a single line, but as you can see, that is not working out for me.

How can I combine the two rows into a single row that presents the Mathematics score next to the reading score? 

SELECT st.test_date, t.name, decode(ts.name,'Mathematics', sts.numscore) as Mathematics,
decode(ts.name, 'Reading', sts.numscore) as Reading
FROM studenttestscore sts
     JOIN studenttest st ON sts.studenttestid = st.id
     JOIN testscore ts ON sts.testscoreid = ts.id
     JOIN test t on ts.testid = t.id
     JOIN students s ON s.id = st.studentid
WHERE t.id IN (601, 602, 603)
     AND s.student_number = '108156'
GROUP BY st.test_date, t.name, 
         decode(ts.name,'Mathematics', sts.numscore),
         decode(ts.name, 'Reading', sts.numscore)
ORDER BY st.test_date

--- CURRENT OUTPUT ---

Test_Date    Name        Mathematics    Reading
29-AUG-13    MAP FALL            227     (null)
29-AUG-13    MAP FALL         (null)        213
22-JAN-14    MAP WINTER          231     (null)
22-JAN-14    MAP WINTER       (null)        229
05-MAY-14    MAP SPRING          238     (null)
05-MAY-14    MAP SPRING       (null)        233

--- DESIRED OUTPUT ---

Test_Date    Name        Mathematics    Reading
29-AUG-13    MAP FALL            227        213
22-JAN-14    MAP WINTER          231        229
05-MAY-14    MAP SPRING          238        233

Upvotes: 0

Views: 87

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270523

You seem to want one name for the date and the name, so only include them in the GROUP BY. Use aggregation functions for the rest:

SELECT st.test_date, t.name,
       MAX(CASE WHEN ts.name = 'Mathematics' THEN sts.numscore END) as Mathematics,
       MAX(CASE WHEN ts.name = 'Reading' THEN sts.numscore END) as Reading
FROM studenttestscore sts JOIN
     studenttest st
     ON sts.studenttestid = st.id JOIN
     testscore ts
     ON sts.testscoreid = ts.id JOIN
     test t
     ON ts.testid = t.id JOIN
     students s
     ON s.id = st.studentid
WHERE t.id IN (601, 602, 603) AND s.student_number = '108156'
GROUP BY st.test_date, t.name, 
ORDER BY st.test_date;

I also replaced decode() with CASE, which is the ANSI standard form for expressing conditions in expressions.

Upvotes: 3

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8497

You have to use Aggergate MAX() and group by name and testdate to get desired result

Sample SQL Fiddle

select t.test_date, 
        t.name, max(Mathematics) as Mathematics,
        max(Reading) as Reading 
from t
GROUP BY t.test_date, t.name
ORDER BY t.test_date;

Upvotes: 1

Related Questions