将不重叠数据的多行合并到一行SQL Server中

Question

我有一个数据表，其中的数据如下：

时间5:506:008:009:00

ID	任务
Jim	睡眠
Jim	唤醒	7:15
Bob	睡眠
鲍勃	brushteth
Bob	吃

正如我在评论中提到的，在CASE表达式周围使用MAX；这被称为条件聚合：

SELECT P.ID, 
CAST(MAX(CASE WHEN DA.Activity = 'sleep' THEN 1 ELSE 0 END) AS bit) AS slept, 
CAST(MAX(CASE WHEN DA.Activity = 'wakeup' THEN 1 ELSE 0 END) AS bit) AS wokeup, 
CAST(MAX(CASE WHEN DA.Activity = 'brushteeth' THEN 1 ELSE 0 END) AS bit) AS brushedteeth, 
CAST(MAX(CASE WHEN DA.Activity = 'eat' THEN 1 ELSE 0 END) AS bit) AS ate
FROM dbo.Person P
LEFT OUTER JOIN dbo.DailyActivities DA ON P.ID = DA.ID
GROUP BY P.ID

此外，正如我在评论中提到的，我取消了对列的3+部分命名，因为它将被弃用。

最简单的方法是使用DISTINCT+子查询+CASE。我稍微简化了你的查询，所以没有联接等，所以我必须少键入一点。但我希望它背后的概念是明确的。

SELECT DISTINCT
p.[ID]
, CASE
WHEN (SELECT COUNT(*) FROM [dbo].[Person] sub WHERE sub.[ID] = p.[ID] AND sub.[Task] = 'slept') > 0 THEN CAST(1 as bit)
ELSE CAST(0 as bit)
END AS [slept]
, CASE
WHEN (SELECT COUNT(*) FROM [dbo].[Person] sub WHERE sub.[ID] = p.[ID] AND sub.[Task] = 'wakeup') > 0 THEN CAST(1 as bit)
ELSE CAST(0 as bit)
END AS [wokeup]
, CASE
WHEN (SELECT COUNT(*) FROM [dbo].[Person] sub WHERE sub.[ID] = p.[ID] AND sub.[Task] = 'brushteeth') > 0 THEN CAST(1 as bit)
ELSE CAST(0 as bit)
END AS [brushteeth]
, CASE
WHEN (SELECT COUNT(*) FROM [dbo].[Person] sub WHERE sub.[ID] = p.[ID] AND sub.[Task] = 'eat') > 0 THEN CAST(1 as bit)
ELSE CAST(0 as bit)
END AS [ate]
FROM [dbo].[Person] p