CODEWITHSUNDEEP
sqlsql-server
Rick
Rick

Reputation: 45

Merge multiple rows without overlapping data into one row SQL Server

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

Upvotes: 0

Views: 459

Answers (2)

Thom A
Thom A

Reputation: 95554

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.

Upvotes: 2

Johannes Krackowizer
Johannes Krackowizer

Reputation: 640

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

Upvotes: 1

Related Questions