I have the following data:

[![enter image description here](https://i.stack.imgur.com/cyUIZ.png)](https://i.stack.imgur.com/cyUIZ.png)

I have the following code:

```

select * from
(
SELECT 
d.CreatedDate,
m.siteid,
m.ProjectNum

FROM DWCorp.SSMaster m 
INNER JOIN DWCorp.SSDetail d ON d.MasterId = m.Id WHERE  ActionId = 7
)as Sourcetable
pivot
(
max(createddate)
for siteid in ([1],[2],[3],[4],[5])
) As pivottable 

```

I would like the data to look like this:

[![enter image description here](https://i.stack.imgur.com/jfu7X.png)](https://i.stack.imgur.com/jfu7X.png)

There will only ever be a maximum of 5 dates. The way I have it at the moment it pivots it by site which I don't want. I want it pivoted by date.

Can anyone help? I know that I may need to use dynamic SQL, but not sure how to do that. I have searched through the forum, but not getting exactly what I am looking for.

Text Output:

```
CreatedDate               siteid ProjectNum
2021-04-06 13:14:01.8933333 20  OTHO00006
2021-04-28 16:40:01.9066667 20  OTHO00006
2021-05-03 22:47:01.7466667 20  OTHO00006
2021-04-28 16:42:02.3700000 20  OTHO00016
2021-05-06 13:27:01.9633333 20  OTHO00016
2021-05-27 15:10:01.7066667 20  OTHO00018
2021-06-29 13:01:01.9266667 20  OTHO00024
2021-05-12 13:38:01.8300000 20  OTHO00024
2021-06-29 13:02:04.7800000 20  OTHO00028
2021-03-25 13:00:03.6100000 21  OBEL00001
2021-08-10 19:44:01.9233333 21  OBEL00003
2021-11-03 20:45:39.2733333 21  OBEL00003
2021-04-26 18:57:34.5533333 21  OBEL00004

```