How to get columns from multiple rows in a single row in SQL

I want to get 2 columns col_a and col_b's values for min and max of some other column. For example:

id	last_updated	col_a	col_b
1	2021-01-01	abc	xyz
1	2021-01-02	abc_0	xyz_0
1	2021-01-03	abc_1	xyz_1
1	2021-01-04	abc_2	xyz_2
2	2021-01-01	abc	xyz
2	2021-01-01	abc	xyz
...

I want to get the result:

|1|abc|abc_2|xyz|xyz_2|  

That is the result of grouping by id, and getting the values of these columns while putting the condition of min and max on some other column(last_updated).

I came up with the following query:

select id, max(last_updated), min(last_updated)  from my_table  group by id  

This gives me the id and min and max dates but not the other 2 columns. I'm not sure how to get the values for the other 2 columns for both dates in same query.

https://stackoverflow.com/questions/65839454/how-to-get-columns-from-multiple-rows-in-a-single-row-in-sql January 22, 2021 at 12:47PM