如何在TSQL中选择行作为视图列？

Question

假设我有3个表：Animal、CareTaker和Apppointment。模式，其中包含一些数据，如：

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

我只想为每只动物选择3名管理员作为一个栏。就像这样：

​

我不关心其他的约会，只关心接下来的三个，每只动物。如果没有三个约会，则可以为空/空。

我很困惑该怎么做。

我尝试过使用Sub查询，如下所示：

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

但是对于第二个看门人，我将不得不在where子句上进行第二级select，以将id从前1中排除(因为不确定如何获得第二行)，类似于select top 1 after excluding first row id; where first row id is (select top 1)情景。

不管怎样，这看起来不是一个很好的方法。

我怎样才能得到想要的输出？

Answer 1

您可以使用以下方法获取行中的所有信息：

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;

然后，你基本上想要把它转成支点。一种方法使用pivot关键字。另一个条件聚合。我更喜欢后者。对于这两种方法，您都需要一个枢轴列，它使用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;