下面的SQL语句生成一个数据透视表,所有这些数据透视表都工作正常。然而,在代码的底部,我试图显示另一个表(availabilitynotes)中的值--在下面的例子中,注释是"A/P“。每一天都会有0或1个笔记。当存在备注时,将显示该备注。当不存在注释时,将显示合并值。但是,如果将1个教师的备注分别设置为星期一和星期三,则会返回重复的行。如何将其设置为返回一行中的所有值?
WITH Bookings AS
( SELECT TeacherID,
[WeekDay] = DATENAME(WEEKDAY, BookingDate),
[Status] = CASE WHEN [2] > 0 THEN 'XXX'
WHEN [0] > 0 AND [1] > 0 THEN 'XXX'
WHEN [0] > 0 THEN 'PM'
WHEN [1] > 0 THEN 'AM'
WHEN [3] > 0 AND CONVERT(time(0), EndTime) <= CONVERT(time(0), '12:00:00') and CONVERT(time(0), StartTime) < CONVERT(time(0), '12:00:00') THEN 'PM'
WHEN [3] > 0 AND CONVERT(time(0), StartTime) >= CONVERT(time(0), '12:00:00') THEN 'AM'
WHEN [3] > 0 AND CONVERT(time(0), StartTime) <= CONVERT(time(0), '12:00:00') AND CONVERT(time(0), EndTime) >= CONVERT(time(0), '12:00:00') THEN 'XXX'
END
FROM ( SELECT TeacherID, BookingDate, BookingDuration, StartTime, EndTime, [X] = 1
FROM BookingDays where (Status = 0 or Status IS NULL)
) BookingDays
PIVOT
( SUM(X)
FOR BookingDuration IN ([0], [1], [2], [3])
) pvt
WHERE BookingDate >= DATEADD(ww, DATEDIFF(ww,0,@Date), 0) AND BookingDate <= DATEADD(ww, DATEDIFF(ww,0,@Date), 6)
), PivotedBookings AS
( SELECT *
FROM Bookings
PIVOT
( MAX([Status])
FOR [WeekDay] IN ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday])
) pvt
)
SELECT t.ID,
t.Firstname,
t.Surname,
CASE WHEN t.Nursery > 0 THEN 'NUR' WHEN t.Reception > 0 THEN 'REC' WHEN t.Year1 > 0 THEN 'Y1' WHEN t.Year2 > 0 THEN 'Y2' WHEN t.Year3 > 0 THEN 'Y3' WHEN t.Year4 > 0 THEN 'Y4' WHEN t.Year5 > 0 THEN 'Y5' WHEN t.Year6 > 0 THEN 'Y6' WHEN t.Year7 > 0 THEN 'Y7' WHEN t.Year8 > 0 THEN 'Y8' WHEN t.Year9 > 0 THEN 'Y9' WHEN t.Year10 > 0 THEN 'Y10' WHEN t.Year11 > 0 THEN 'Y11' WHEN t.ALevel > 0 THEN 'ALevel' END + ' - ' + CASE WHEN t.ALevel > 0 THEN 'ALevel' WHEN t.Year11 > 0 THEN 'Y11' WHEN t.Year10 > 0 THEN 'Y10' WHEN t.Year9 > 0 THEN 'Y9' WHEN t.Year8 > 0 THEN 'Y7' WHEN t.Year6 > 0 THEN 'Y6' WHEN t.Year5 > 0 THEN 'Y6' WHEN t.Year4 > 0 THEN 'Y4' WHEN t.Year3 > 0 THEN 'Y3' WHEN t.Year2 > 0 THEN 'Y2' WHEN t.Year1 > 0 THEN 'Y1' WHEN t.Reception > 0 THEN 'REC' WHEN t.Nursery > 0 THEN 'NUR' ELSE '' END as 'KeyStage',
Monday = CASE WHEN an.Date = DATEADD(ww, DATEDIFF(ww,0,@Date), 0) AND an.TeacherID = t.ID THEN an.Text WHEN t.Status = 0 THEN 'XXX' ELSE COALESCE(pb.Monday, '') END,
Tuesday = CASE WHEN an.Date = DATEADD(ww, DATEDIFF(ww,0,@Date), 1) AND an.TeacherID = t.ID THEN an.Text WHEN t.Status = 0 THEN 'XXX' ELSE COALESCE(pb.Tuesday, '') END,
Wednesday = CASE WHEN an.Date = DATEADD(ww, DATEDIFF(ww,0,@Date), 2) AND an.TeacherID = t.ID THEN an.Text WHEN t.Status = 0 THEN 'XXX' ELSE COALESCE(pb.Wednesday, '') END,
Thursday = CASE WHEN an.Date = DATEADD(ww, DATEDIFF(ww,0,@Date), 3) AND an.TeacherID = t.ID THEN an.Text WHEN t.Status = 0 THEN 'XXX' ELSE COALESCE(pb.Thursday, '') END,
Friday = CASE WHEN an.Date = DATEADD(ww, DATEDIFF(ww,0,@Date), 4) AND an.TeacherID = t.ID THEN an.Text WHEN t.Status = 0 THEN 'XXX' ELSE COALESCE(pb.Friday, '') END
FROM Teachers t
LEFT JOIN PivotedBookings pb
ON pb.TeacherID = t.ID
LEFT JOIN TeacherBands tb
ON tb.ID = t.Band
LEFT JOIN AvailabilityNotes an
ON t.ID = an.TeacherID
WHERE t.Active = 0 and (t.Status = 1 or t.Status = 0) and t.PrimarySchool = 1
ORDER BY t.Surname, t.Firstname asc这将生成以下输出-
ID | Firstname | Surname | Mon | Tue | Wed | Thu | Fri
1 Steve Smith XXX PM AM A/P NULL
1 Steve Smith XXX PM AM NULL A/P当我需要的时候-
ID | Firstname | Surname | Mon | Tue | Wed | Thu | Fri
1 Steve Smith XXX PM AM A/P A/P谢谢
发布于 2013-03-14 11:26:11
您需要每一天的有效值,如果存在,则该值将大于NULL。
select ID, Firstname, Surname
, max(Monday) as Monday, -- etc
from ( your giant query ) as Q
group by ID, Firstname, Surname应该能行得通。
https://stackoverflow.com/questions/15380988
复制相似问题