?
Solved

consolidate 4 lines of oracle query output to 1 line

Posted on 2016-09-29
4
Medium Priority
?
74 Views
Last Modified: 2016-09-29
I've written the query below and it outputs 4 lines of data.  I need to consolidate the output to a single line, can someone help me out?  Thanks!!!

Query
SELECT 
distinct s.student_number as StuNum,
case when sts.testscoreid = '301' THEN sts.numscore else null end as EBRW_Total,
case when sts.testscoreid = '120' THEN sts.numscore else null end as Math_Total,
case when sts.testscoreid = '122' THEN sts.numscore else null end as Total_Score
FROM students s
LEFT JOIN StudentTestScore sts ON sts.StudentID = s.ID
LEFT JOIN StudentTest st ON sts.StudentTestID = st.ID
LEFT JOIN Test t ON st.TestID = t.ID
WHERE s.student_number = '12345' AND t.Name = 'SAT' AND sts.NumScore > 0

Open in new window

My Current Results
StuNum,EBRW_Total,Math_Total,Total_Score
12345,,,1010
12345,,450,
12345,560,,
12345,,,,

Open in new window

Desired results
StuNum,EBRW_Total,Math_Total,Total_Score
12345,560,450,1010

Open in new window

0
Comment
Question by:Basssque
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 41821911
See if this works:
SELECT 
distinct s.student_number as StuNum,
max(case when sts.testscoreid = '301' THEN sts.numscore else null end) as EBRW_Total,
max(case when sts.testscoreid = '120' THEN sts.numscore else null end) as Math_Total,
max(case when sts.testscoreid = '122' THEN sts.numscore else null end) as Total_Score
FROM students s
LEFT JOIN StudentTestScore sts ON sts.StudentID = s.ID
LEFT JOIN StudentTest st ON sts.StudentTestID = st.ID
LEFT JOIN Test t ON st.TestID = t.ID
WHERE s.student_number = '12345' AND t.Name = 'SAT' AND sts.NumScore > 0
group by s.student_number

Open in new window

0
 

Author Comment

by:Basssque
ID: 41821916
That works great!
Can you explain the use of max(), I'm not sure I understand how it's being used here.  Thanks!!
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 total points
ID: 41821920
For every row you only ever return one value per column.

For example, EBRW_Total will only have a value in one of the 4 rows.  MAX ignores nulls so the MAX value is the only value returned.
0
 

Author Closing Comment

by:Basssque
ID: 41821923
Thank you so much!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question