select first and last record of each group horizontally

Question

I have a table like

i want to select first and last record of every group by facility_id and created_at horizontally need to output like . i can do it vertically but need horizontally

Answer 1

I think this is much simpler using window functions and select distinct:

select distinct facility_id, name,
       first_value(value) over (partition by facility_id, name order by created_at asc) as first_value,
       min(created_at) as first_created_at,
       first_value(value) over (partition by facility_id, name order by created_at desc) as last_value,
       max(created_at) as last_created_at
from t;

No subqueries. No joins.

You can also use arrays to accomplish the same functionality, using group by. It is a shame that SQL Server doesn't directly support first_value() as a window function.