Merge multiple rows without overlapping data into one row SQL Server-CodePudding

I have a data table with data like this:

ID	Task	time
Jim	sleep	5:50
Jim	wakeup	7:15
Bob	sleep	6:00
Bob	brushteeth	8:00
Bob	eat	9:00

and I am running a query to generate the following below

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

ID	slept	wokeup	brushedteeth	ate
Jim	TRUE	FALSE	FALSE	FALSE
Jim	FALSE	TRUE	FALSE	FALSE
Bob	TRUE	FALSE	FALSE	FALSE
Bob	FALSE	FALSE	TRUE	FALSE
Bob	FALSE	FALSE	FALSE	TRUE

How could we get the results for 1 ID per row like below

ID	slept	wokeup	brushedteeth	ate
Jim	TRUE	TRUE	FALSE	FALSE
Bob	TRUE	FALSE	TRUE	TRUE

CodePudding user response：

As I mentioned in the comments, use MAX around your CASE expressions; this is known as conditional aggregation:

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

Also, as I mentioned in the comments, I got rid of the 3 part naming on the column as it will be deprecated.

CodePudding user response：

The easist way is use DISTINCT Subqueries CASE. I simplified your query a little bit so no join etc. so I have to type a little less. But i hope the concept behind it is clear.

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