How to Write a Pivot Table Query

Question

I have this table:

CREATE TABLE [dbo].[Notifications](
	FullName varchar(50) NOT NULL,
	MessageID int NOT NULL,
	Sent date NOT NULL)

Each "FullName" will have several rows with different MessageIDs and Sent dates. MessageID can only have four values: 97, 98, 99, 100.

I am trying to pivot this information into this form:

+----------+--------+--------+---------+---------+
| FullName |   97   |   98   |   99    |   100   |
+----------+--------+--------+---------+---------+
| Sam      | 1/3/20 | 2/3/21 | 4/12/22 | 2/23/23 |
| Alice    | 2/5/21 | 4/8/22 | 1/23/23 | 2/21/24 |
+----------+--------+--------+---------+---------+

Where the columns contain the values of "Sent" and are in increasing order for each FullName. I've looked at several web sites related to PIVOT but have been unable to put all the pieces together.

Answer 1

For example, to get the latest Sent (if there are several rows with the same FullName and MessageID), try this query:

select FullName, [97], [98], [99], [100]
from 
(
	select FullName, MessageID, [Sent] from Notifications 
) as s
pivot
(
   max([Sent]) for MessageID in ( [97], [98], [99], [100] )
) as p
order by [97]

If something else is necessary, show the data.

Answer 2

Use conditional aggregation to pivot the four known MessageID values into columns and pick the earliest (or latest) Sent date per person.

For the earliest Sent date per MessageID and FullName:

SELECT
    FullName,
    MIN(CASE WHEN MessageID = 97  THEN Sent END) AS [97],
    MIN(CASE WHEN MessageID = 98  THEN Sent END) AS [98],
    MIN(CASE WHEN MessageID = 99  THEN Sent END) AS [99],
    MIN(CASE WHEN MessageID = 100 THEN Sent END) AS [100]
FROM dbo.Notifications
GROUP BY FullName
ORDER BY FullName;

If instead the requirement is to show the most recent Sent date for each MessageID and FullName, replace MIN with MAX:

SELECT
    FullName,
    MAX(CASE WHEN MessageID = 97  THEN Sent END) AS [97],
    MAX(CASE WHEN MessageID = 98  THEN Sent END) AS [98],
    MAX(CASE WHEN MessageID = 99  THEN Sent END) AS [99],
    MAX(CASE WHEN MessageID = 100 THEN Sent END) AS [100]
FROM dbo.Notifications
GROUP BY FullName
ORDER BY FullName;

This produces one row per FullName with four date columns named 97, 98, 99, and 100.

---

References:

• Pivot Transformation
• Make summary data easier to read by using a crosstab query
• Table.Pivot