sql - 合并具有相同日期的数据行

Question

我一直在处理从 5 个不同表中选择的查询:

SELECT  R.[Name],
        P.[Name],
        S.[Number],
        T.[Name],
        D.[Hours],
        D.[ActivityDate]
FROM DailyTaskHours D
INNER JOIN Task T
    ON D.TaskId = T.PK_Task
INNER JOIN Story S
    ON T.StoryId = S.PK_Story
INNER JOIN Sprint P
    ON S.SprintId = P.PK_Sprint
INNER JOIN Product R
    ON P.ProductId = R.PK_Product

这是视觉上的结果:

最后两列称为 Hours 和 ActivityDate。从表中可以看出，对于 Number - OPA-76，有两个条目:

我希望它是一行而不是两行。我希望有 7 列而不是最后两列标记为星期日到星期六，并且小时的两个条目根据它们的 ActivityDate 在一周中的正确日期。

每天只能输入1次。

我怎样才能做到这一点？

Answer 1

尝试这样的事情:

SELECT R.[Name], P.[Name], S.[Number], T.[Name],
   sum(CASE WHEN DatePart(dw, D.ActivityDate) = 1 THEN D.[Hours] ELSE 0 END) AS Monday,
   sum(CASE WHEN DatePart(dw, D.ActivityDate) = 2 THEN D.[Hours] ELSE 0 END) AS Tuesday,
   sum(CASE WHEN DatePart(dw, D.ActivityDate) = 3 THEN D.[Hours] ELSE 0 END) AS Wednesday,
   sum(CASE WHEN DatePart(dw, D.ActivityDate) = 4 THEN D.[Hours] ELSE 0 END) AS Thursday,
   sum(CASE WHEN DatePart(dw, D.ActivityDate) = 5 THEN D.[Hours] ELSE 0 END) AS Friday,
   sum(CASE WHEN DatePart(dw, D.ActivityDate) = 6 THEN D.[Hours] ELSE 0 END) AS Saturday,
   sum(CASE WHEN DatePart(dw, D.ActivityDate) = 7 THEN D.[Hours] ELSE 0 END) AS Sunday
FROM DailyTaskHours D
INNER JOIN Task T ON D.TaskId = T.PK_Task
INNER JOIN Story S ON T.StoryId = S.PK_Story
INNER JOIN Sprint P ON S.SprintId = P.PK_Sprint
INNER JOIN Product R ON P.ProductId = R.PK_Product
GROUP BY R.[Name], P.[Name], S.[Number], T.[Name] 

在这里阅读关于工作日的注释:

http://technet.microsoft.com/en-us/library/aa258265(v=SQL.80).aspx