How to select rows as a column for View in TSQL?

Question

Assume I have 3 tables: Animal, CareTaker, and Apppointment. Schema, with some data like so:

Create Table Animal (Id int identity, Name varchar(25))
Create Table CareTaker(Id int identity, Name varchar(50))
Create Table Appointments(Id int identity, AnimalId int, CareTakerId int, AppointmentDate DateTime, BookingDate DateTime)

Insert into Animal(Name) Values('Ghost'), ('Nymeria'), ('Greywind'), ('Summer')
Insert into CareTaker(Name) Values ('Jon'), ('Arya'), ('Rob'), ('Bran') 

Insert into Appointments(AnimalId, CareTakerId, AppointmentDate, BookingDate) Values
(1, 1, GETDATE() + 7, GetDate()), -- Ghost cared by Jon
(1, 2, GETDATE() + 6, GetDate()), -- Ghost cared by Arya
(4, 3, GETDATE() + 8, GetDate()) -- Summer cared by Rob

I want to select only 3 caretakers for each animal as a columns. Something like this:

I don't care about other appointments, just the next three, for each animal. If there aren't three appointments, it can be blank / null.

I'm quite confused about how to do this.

I tried it with Sub queries, something like so:

select Name, 
    -- Care Taker 1
    (Select Top 1 C.Name
    From Appointments A
        Join CareTaker C on C.Id = A.CareTakerId
    Where A.AppointmentDate > GETDATE()
        And A.AnimalId = Animal.Id
    Order By AppointmentDate) As CareTaker1,
    -- Appointment Date 1
    (Select Top 1 AppointmentDate
    From Appointments
    Where AppointmentDate > GETDATE()
        And AnimalId = Animal.Id
    Order By AppointmentDate) As AppointmentDate1
From Animal

But for the second caretaker, I would have to go second level select on where clause to exclude the id from top 1 (because not sure how else to get second row), something like select top 1 after excluding first row id; where first row id is (select top 1) situtation.

Anyhow, that doesn't look like a great way to do this.

How can I get the desired output please?

Answer 1

You can get all the information in rows using:

select an.name as animal, ct.name as caretaker, a.appointmentdate
from appointments a join
     animals an
     on a.id = an.animalid join
     caretaker c
     on a.caretakerid = c.id;

Then, you basically want to pivot this. One method uses the pivot keyword. Another conditional aggregation. I prefer the latter. For either, you need a pivot column, which is provided using row_number():

select animal,
       max(case when seqnum = 1 then caretaker end) as caretaker1,
       max(case when seqnum = 1 then appointmentdate end) as appointmentdate1,
       max(case when seqnum = 2 then caretaker end) as caretaker2,
       max(case when seqnum = 2 then appointmentdate end) as appointmentdate2,
       max(case when seqnum = 3 then caretaker end) as caretaker3,
       max(case when seqnum = 3 then appointmentdate end) as appointmentdate3
from (select an.name as animal, ct.name as caretaker, a.appointmentdate,
             row_number() over (partition by an.id order by a.appointmentdate) as seqnum
      from appointments a join
           animals an
           on a.id = an.animalid join
           caretaker c
           on a.caretakerid = c.id
     ) a
group by animal;