CODEWITHSUNDEEP
sqldatabaseoracle-database
Dong Cao-Huu
Dong Cao-Huu

Reputation: 53

Flatten out repeating rows - Oracle SQL

Because each course may have several instructors, my query currently returns multiple rows for a course. I'm trying to flatten out the result by having additional columns for Primary Instructor, Instructor 2 and 3 (though a course may have more than 3 instructors). Please see below.

FROM:

|  Course  |  Instructor  | Primary? |
|----------|--------------|----------|
|  CS 300  |   John Doe   |    Y     |
|  CS 300  |  Jane Public |          |
| ECON 101 |  Richard Roe |    Y     |

TO:

|  Course  |  Primary_Instructor  | Instructor2 | Instructor3 | 
|----------|----------------------|-------------|-------------|
|  CS 300  |       John Doe       | Jane Public |             |
| ECON 101 |      Richard Roe     |             |             |

I also provides here a simple query that hopefully shows the structure of the tables and how they're connected:

SELECT COURSE.TITLE "Course", 
       PERSON.FIRST_NAME ||' '|| PERSON.LAST_NAME "Instructor",
       INSTRUCTOR.PRIMARY_IND "Primary?"
FROM   COURSE JOIN INSTRUCTOR ON COURSE.COURSE_KEY = INSTRUCTOR.COURSE_KEY
       JOIN PERSON ON INSTRUCTOR.ID = PERSON.ID

I thought of PIVOT but I don't have specific values of the column to pivot on.

UPDATE:

I've found a solution based on Tejash answer, which is slightly changed to fit what I need:

SELECT * FROM
(SELECT COURSE.TITLE "Course", 
        PERSON.FIRST_NAME ||' '|| PERSON.LAST_NAME "Instructor",
        ROW_NUMBER() OVER (PARTITION BY INSTRUCTOR.COURSE_KEY 
        ORDER BY INSTRUCTOR.PRIMARY_IND) AS INS_ORDER
 FROM   COURSE JOIN INSTRUCTOR ON COURSE.COURSE_KEY = INSTRUCTOR.COURSE_KEY
        JOIN PERSON ON INSTRUCTOR.ID = PERSON.ID)
PIVOT
(MAX("Instructor") FOR INS_ORDER IN (1 AS PRIMARY_INSTRUCTOR, 2 AS INSTRUCTOR2, 3 AS INSTRUCTOR3))

Upvotes: 0

Views: 428

Answers (2)

Popeye
Popeye

Reputation: 35930

You can use the PIVOT for including only 3 instructors as follows:

SELECT * FROM
(SELECT COURSE.TITLE "Course", 
       PERSON.FIRST_NAME ||' '|| PERSON.LAST_NAME "Instructor",
       CASE WHEN INSTRUCTOR.PRIMARY_IND = 'Y' THEN 1 
            ELSE ROW_NUMBER() OVER (PARTITION BY COURSE.COURSE_KEY ORDER BY 1) + 1 
       END AS PRIMARY_
FROM   COURSE JOIN INSTRUCTOR ON COURSE.COURSE_KEY = INSTRUCTOR.COURSE_KEY
       JOIN PERSON ON INSTRUCTOR.ID = PERSON.ID)
PIVOT
(MAX("Instructor") FOR PRIMARY_ IN (1 AS PRIMARY_INSTRUCTOR,2 AS INSTRUCTOR2,3 AS INSTRUCTOR2))

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143163

Unknown number of instructors makes it not-that-easy.

However, if you can live with "other instructors" in the same column, then it gets way simpler. See if it helps.

SQL> with test (course, instructor, primary) as
  2    (select 'CS 300'  , 'John Doe'   , 'Y'  from dual union all
  3     select 'CS 300'  , 'Jane Public', null from dual union all
  4     select 'CS 300'  , 'Little Foot', null from dual union all
  5     select 'ECON 101', 'Richard Roe', 'Y'  from dual
  6    )
  7  select course,
  8    max(case when primary = 'Y' then instructor end) primary_instructor,
  9    listagg(case when primary is null then instructor end, ', ')
 10      within group (order by instructor) other_instructors
 11  from test
 12  group by course;

COURSE   PRIMARY_INSTRUCTOR   OTHER_INSTRUCTORS
-------- -------------------- --------------------------------------------------
CS 300   John Doe             Jane Public, Little Foot
ECON 101 Richard Roe

SQL>

Upvotes: 3

Related Questions