CODEWITHSUNDEEP
sqloracle-database
MasonS
MasonS

Reputation: 15

How to show only one line of data

I am doing some research to see if certain employees have completed required training. When I run the script I get an output for each individual course instead of one line for each employee.

My desired result would look like this:

Employee Course Course Course
Employee1 Course 1 Course 2 Course3
Employee2 Course 1 Course 2 Course3
Employee3 Course 1 Course 2 Course3
Employee4 Course 1 Course 2 Course3

What I am currently getting:

Employee Course Course Course
Employee1 Course 1 Course 2 Course3
Employee1 Course 1 Course 2 Course3
Employee1 Course 1 Course 2 Course3

This is my current query:

select distinct
    im.employee_id,
    im.last_name,
    im.first_name,
    case when sc.course1 = 'ABC'
        then 1 else 0 end "Course Name",
    case when sc.course2 = 'DEF'
        then 1 else 0 end "Course Name",
    case when sc.course3 = 'GHI'
        then 1 else 0 end "Course Name"
from
    employee_table im
    join
    course_table sc
    on
    im.employee_id= sc.employee_id

Upvotes: 1

Views: 96

Answers (1)

nbk
nbk

Reputation: 49395

I doubt that your query will return that result, but you can use aggregation to get "1 row"

select 
    im.employee_id,
    im.last_name,
    im.first_name,
    MAX(case when sc.course1 = 'ABC'
        then 1 else 0 end) "Course Name1",
    MAX(case when sc.course2 = 'DEF'
        then 1 else 0 end) "Course Name2",
    MAX(case when sc.course3 = 'GHI'
        then 1 else 0 end) "Course Name3"
from
    employee_table im
    join
    course_table sc
    on
    im.employee_id= sc.employee_id 
GROUP BY  im.employee_id, im.last_name, im.first_name

Upvotes: 1

Related Questions