2021腾讯云限时秒杀，爆款1核2G云服务器298元/3年！（领取2860元代金券），
地址：https://cloud.tencent.com/act/cps/redirect?redirect=1062

2021阿里云最低价产品入口+领取代金券(老用户3折起)，
入口地址：https://www.aliyun.com/minisite/goods

推荐：How to forcely drop tables in SQL Server 2008

This script can drop a SQL Server table. It automaticly remove all the constraints of the table before the drop query. 先删除所有constraints declare @

up vote 2 down vote favorite 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? sql-server-2008 tsql
  |
  this question asked Apr 22 '16 at 11:33 LocustHorde 2,290 11 45 75

up vote 2 down vote ---Accepted---Accepted---Accepted---

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

推荐：SQL Server 2008 function types in T-SQL

SQL Server 2008 function types in T-SQL from： http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1327000,00.html

15 Oct 2008 | SearchS

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;

  |
  this answer answered Apr 22 '16 at 11:45 Gordon Linoff 651k 24 235 336 Hi, thank you, I am looking into Pivot tables now.. your query doesn't quite do what I want: i.imgur.com/jQrcydK.png Top is the sub-query from my original post, and bottom is your query –  LocustHorde Apr 22 '16 at 13:17 @LocustHorde . . . Unfortunately, I cannot build a SQL Fiddle. However, this should be returning one row per animal along with up to three appointments. –  Gordon Linoff Apr 24 '16 at 20:20 hi, yea, SQL Fiddle has been broken for me for a while, I can't build or load any existing urls. Can you please explain what seqnum and row_number() is doing? The query doesn't work and I don't understand how it is transposing rows to columns –  LocustHorde Apr 25 '16 at 12:11 Oh actually, I worked it out, you were joining on wrong ids in the inner select.. still, it would be helpful if you can explain what this line is doing: row_number() over (partition by a.Id order by t.AppointmentDate) as seqnum and how it's relation to max(case when seqnum = 1 then caretaker end) as caretaker1, - thanks –  LocustHorde Apr 25 '16 at 12:24 1 row_number() is a window function that enumerates rows; you should start with the documentation. The max( . . . ) is just conditional aggregation using the sequential value. –  Gordon Linoff Apr 26 '16 at 1:37

推荐：How to Set Up SQL Server 2008 FileStream In the Cluster

Today i meet a very odd problem. i use sql server 2008 filestream in the cluster, when i remove a sql instance form one node to another, the operator