How to show only one line of data

Question

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

The design in course_table is strange. You have a column for each course? (sc.course1, sc.course2, sc.course3). Why not have a course_id column instead? — Isolated, Oct 03 '22 at 19:23
Does this answer your question? [Oracle SQL pivot query](https://stackoverflow.com/questions/4841718/oracle-sql-pivot-query) — astentx, Oct 04 '22 at 07:49

nbk · Accepted Answer · 2022-10-04T07:18:44.860

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

How to show only one line of data

1 Answers1