Splitting Rows into Columns - Dates to Days with matching Data

Question

I have the following data:

PersonID	Event	Date	Name	Status
1	Event A	31/12/1900	John	Pending
1	Event A	01/01/1901	John	Pending
2	Event B	31/12/1900	Sarah	Pending
2	Event B	01/01/1901	Sarah	Complete

I would like to SELECT this data based on date and other parameters and display the following with each date being matched to its individual day and displaying the matching status, I've just started with Monday/Tuesday to attempt this, but will expand to Monday - Friday.

PersonID	Event	Name	Monday	Tuesday
1	Event A	John	Pending	Pending
2	Event B	Sarah	Pending	Complete

I've written the following, trying to proof of concept this on Monday/Tuesday columns. I admit, this is a simplified version there are LEFT JOIN's to add in the peoples name from a details table, but I don't believe that should affect why this isn't working for me. (?!)

SELECT
       [PersonID]
      ,[event]
      ,[name]
      ,[status] AS Monday
      ,NULL AS Tuesday
 
  FROM [People]

  WHERE cast([eventdate] as date) = '1900-12-31'

  UNION ALL

  SELECT
       [PersonID]
      ,[event]
      ,[name]
      ,NULL AS Monday
      ,[status] AS Tuesday
 
  FROM [People]

  WHERE cast([eventdate] as date)  = '1901-01-01'

Instead this is showing the following:

PersonID	Event	Name	Monday	Tuesday
1	Event A	John	Pending	NULL
1	Event A	John	NULL	Pending
2	Event B	Sarah	Pending	NULL
2	Event B	Sarah	NULL	Complete

I guess I need a little nudge in the right direction on how I might achieve this?

Thanks

score 0 · Answer 1 · answered Jul 30 '22 at 19:48

You can use GROUP BY and MAXto pivot your table

SELECT
[PersonID],[Name], [Event],
MAX(CASE WHEN DATEPART(DW,[Date]) = 2 then [Status] ELSe '' ENd) [Monday],
MAX(CASE WHEN DATEPART(DW,[Date]) = 3 then [Status] ELSE '' ENd) [Tuesday],
MAX(CASE WHEN DATEPART(DW,[Date]) = 4 then [Status] ELSE '' ENd) [Wednesday],
MAX(CASE WHEN DATEPART(DW,[Date]) = 5 then [Status] ELSE '' ENd) [Thursday],
MAX(CASE WHEN DATEPART(DW,[Date]) = 6 then [Status] ELSE '' ENd) [Friday]
FROM tab1
GROUP BY
[PersonID],[Name], [Event]

PersonID | Name  | Event   | Monday  | Tuesday  | Wednesday | Thursday | Friday
-------: | :---- | :------ | :------ | :------- | :-------- | :------- | :-----
       1 | John  | Event A | Pending | Pending  |           |          |       
       2 | Sarah | Event B | Pending | Complete |           |          |

db<>fiddle here

Splitting Rows into Columns - Dates to Days with matching Data

1 Answers1