CODEWITHSUNDEEP
sqlsql-servert-sql
Pure.Krome
Pure.Krome

Reputation: 86997

How can I flatten part of this Sql Query? Does this need a PIVOT?

I'm struggling to flatten part of my sql query. I'm not sure if I require a PIVOT or this should be attempted with a sub query using OVER.

Now, I can't use my real table names, so I've created a very contrite example that represents our real system. It looks like homework, but it's not.

This is the reproduction (*note: I tried to make this a SqlFiddle but it just wasn't working) :

-- Some fake courses/subjects.
DECLARE @Courses TABLE (
    CourseId INTEGER IDENTITY NOT NULL,
    Name VARCHAR(100) NOT NULL)

INSERT INTO @Courses VALUES ('CS100')
INSERT INTO @Courses VALUES ('PY300')
INSERT INTO @Courses VALUES ('AG201')

-- Some teachers that teach the subject/course.
-- Notice there is no ORDER column, so the first teacher is considered
-- The main teacher. the 2nd result is the backup teacher.
-- First teacher = ORDER BY TeacherId ASC (per course)
DECLARE @Teachers TABLE (
    TeacherId INTEGER IDENTITY NOT NULL,
    CourseId INTEGER NOT NULL,
    Name VARCHAR(100)
)

INSERT INTO @Teachers VALUES (1, 'Jane')
INSERT INTO @Teachers VALUES (1, 'John')
INSERT INTO @Teachers VALUES (2, 'Bill')
INSERT INTO @Teachers VALUES (2, 'Anne')
INSERT INTO @Teachers VALUES (3, 'R2D2')

/*
-- Expected output:

CourseId | Cource Name | Primary Teacher | Backup Teacher
----------------------------------------------------------
1        | CS100       | Jane            | John
2        | PY300       | Bill            | Anne
3        | AG201       | R2D2            | <NULL>
*/

Can anyone provide some suggestions please?

Finally, please don't make schema suggestions, like the teachers could be in there own table and then have a join/link table for Courses and Teachers (because the same teacher can teach more than one class, right?).

Upvotes: 0

Views: 106

Answers (1)

xLiSPirit
xLiSPirit

Reputation: 390

You can try this query:

;with cte as(
select *, row_number() over(partition by CourseId order by TeacherId ASC) as rn
  from @Teachers)

select c.CourseId
     , c.Name as [Cource Name]
     , t1.Name as [Primary Teacher]
     , t2.Name as [Backup Teacher]
  from @Courses c 
      left outer join cte t1 on c.CourseId = t1.CourseId and t1.rn = 1
      left outer join cte t2 on c.CourseId = t2.CourseId and t2.rn = 2

Upvotes: 1

Related Questions