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

Question

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.

Popeye · Accepted Answer · 2021-01-22 05:05:49Z

2

You can use MIN and MAX analytical function as follows:

select id,
       max(case when mindt = last_updated then col_a end) as min_col_a,
       max(case when maxdt = last_updated then col_a end) as max_col_a,
       max(case when mindt = last_updated then col_b end) as min_col_b,
       max(case when maxdt = last_updated then col_b end) as max_col_b
  from
(select t.*,
       min(last_updated) over (partition by id) as mindt,
       max(last_updated) over (partition by id) as maxdt
  from your_table t) t
group by id

answered Jan 22, 2021 at 5:05

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2021-01-22 05:02:51Z

2

We can use ROW_NUMBER, twice, to find the first and last rows, as ordered by last_updated, for each id group of records. Then, aggregate by id and pivot out columns for the various col_a and col_b values.

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY last_updated) rn_min,
              ROW_NUMBER() OVER (PARTITION BY id ORDER BY last_updated DESC) rn_max
    FROM yourTable
)

SELECT
    id,
    MAX(CASE WHEN rn_min = 1 THEN col_a END) AS col_a_min,
    MAX(CASE WHEN rn_max = 1 THEN col_a END) AS col_a_max,
    MAX(CASE WHEN rn_min = 1 THEN col_b END) AS col_b_min,
    MAX(CASE WHEN rn_max = 1 THEN col_b END) AS col_b_max
FROM cte
GROUP BY id;

edited Jan 22, 2021 at 5:02

answered Jan 22, 2021 at 4:55

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

2 Comments

ayushgp Over a year ago

How can I get these in separate columns? I've updated the question

Tim Biegeleisen Over a year ago

Answer updated. Your original question made it seem that you wanted a CSV string.

Dale K · Accepted Answer · 2021-01-22 05:07:12Z

2

Not the neatest solution but demonstrates another way to obtain the data you want. We join the table on itself as we normally want data from 2 rows, then we use cross apply to restrict it to first and last.

select T1.id, T2.col_a, T1.col_a, T2.col_b, T1.col_b
from #my_table T1
inner join #my_table T2 on T1.id = T2.id
cross apply (
  select id, max(last_updated) MaxLastUpdated, min(last_updated) MinLastUpdated
  from #my_table
  group by id
) X
where T1.last_updated = X.MaxLastUpdated and T2.last_updated = X.MinLastUpdated;

With the sample data provided this appear to perform worse than the row_number() solution. The fastest solution is the analytical functions.

edited Jan 22, 2021 at 5:07

answered Jan 22, 2021 at 5:01

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

Comments

Dale K · Accepted Answer · 2021-01-22 05:38:14Z

0

select id,max(last_updated) last_updatedMax, min(last_updated) last_updatedMin,max(col_aMax) col_aMax, max(col_aMin) col_aMin,max(col_bMax) col_bMax, max(col_bMin) col_bMin
from
(
    select 
        *
        , first_value(col_a) OVER (PARTITION BY  id ORDER BY last_updated desc) as  col_aMax
        , first_value(col_a) OVER (PARTITION BY  id ORDER BY last_updated asc) as  col_aMin
        , first_value(col_b) OVER (PARTITION BY  id ORDER BY last_updated desc) as  col_bMax
        , first_value(col_b) OVER (PARTITION BY  id ORDER BY last_updated asc) as  col_bMin
     from my_table
) t
group by id

edited Jan 22, 2021 at 5:38

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Jan 22, 2021 at 5:35

Nidhin HS

264 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related