Re-aligning dataset so that different period appear on the same row

I have each entity data over three different [periods]. These are: 2022-03-31, 2021-09-30 and 2021-03-31

I want to alter the dataset and have

2022-03-31  AS [curr_period], 2021-09-30 AS [prev_period] and 2021-03-31 AS [prev2_period].

This can as such easily done by case statements.

The problem is, I want that data to be aligned, so that [entity] appears and then on the same line [curr_period], [prev_period], [prev2_period]

How can this be achieved?

Row (over) ? But I don’t think it would actually be doing what I want. What other method is there?

Answer

When you say alter database, do you mean to create a new table for this entity or are you just looking to have a select query? Do you know those dates for certain or do you also need to sort them by date and get first, second and third?

If you know these dates for certain and they don’t change then why do you use them as hard-coded like this

SELECT entity, '2022-03-31'  AS [curr_period], '2021-09-30' AS [prev_period], '2021-03-31' AS [prev2_period]
FROM entities
Group by entity

I might be missing something but it’s hard to know for sure without example data.

This works for me without hard-coded dates.

SELECT e1.entity, 
e1.date AS [curr_period], 
e2.date AS [prev_period], 
e3.date AS [prev2_period]
FROM entities e1
join entities e2 on e2.entity = e1.entity and e1.date > e2.date
join entities e3 on e3.entity = e1.entity and e2.date > e3.date
order by e1.entity, e1.date desc