Reshape long to wide table in SQL

I have a dataset with variables ID, Vaccine, and Date. Some Vaccines have a single date, and some vaccines have two or three dates for a single ID. When I create a table in SQL, multiple rows are created for the IDs that have two or more dates for a vaccine. See an example below:

How can I create a table as below in SQL?

Thanks

You can try something like this:

SELECT 
    ID,
    Vaccine,
    MAX(CASE WHEN rn = 1 THEN Date END) AS Date1,
    MAX(CASE WHEN rn = 2 THEN Date END) AS Date2,
    MAX(CASE WHEN rn = 3 THEN Date END) AS Date3
FROM (
    SELECT 
        ID,
        Vaccine,
        Date,
        ROW_NUMBER() OVER (PARTITION BY ID, Vaccine ORDER BY Date) AS rn
    FROM 
        YourTableName
) AS subquery
GROUP BY 
    ID, Vaccine;

In this query:

1. The inner subquery assigns a row number (rn) to each row within the same ID and Vaccine partition, ordered by the Date.
2. The outer query uses CASE expressions to pivot the dates based on their row number (rn). The MAX() function is used to aggregate the dates for each ID and Vaccine combination.

Make sure to replace YourTableName with the actual name of your table. This query will transform the data into the format you desire, with Date1, Date2, and Date3 columns indicating the different dates for each ID and Vaccine combination.