Merging Rows and put values in different columns

Question

I have the following table:

 ID   Booked         Date
100        0   2021-01-18
100        0   2020-10-05 
162        0   2021-01-14 
162        0   2021-01-01 
162        1   2020-12-30 
162        1   2020-10-01 
266        0   2021-01-14 
266        0   2020-10-05 
266        1   2020-11-06

and want to get to this result:

 ID  BookedYMIN   BookedYMAX   BookedNMIN   BookedNMAX
100        NULL         NULL   2020-10-05   2021-01-18
162  2020-10-01   2021-12-30   2020-01-01   2021-01-14
266  2020-11-06   2020-11-06   2020-10-05   2021-01-18

An ID value in the first table occurs max 4 times:

Booked + 1 = was either marked booked the first time in database OR was either marked booked the last time in database

Booked + 0 = was either marked not-booked the first time in database OR was either marked not-booked the last time in database

In python I am able to manage this problem, but I face performance issues. Maybe someone knows how I can handle this in SQL Server Management Studio to achieve a speedup.

Isn't this just conditional aggregation? The condition being on the column `Booked`. What *have* you tried? Why didn't it work? What does the above have to do with the DML operation `MERGE`? — Thom A, Aug 02 '21 at 12:50
the word merge i just used to tell i want to put together the rows with same id — sebk, Aug 02 '21 at 12:55
[[tag:merge]] is a specific operation in SQL though, also known as an Upsert. You aren't looking to use a `MERGE` here. — Thom A, Aug 02 '21 at 12:57
Does this answer your question? [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — astentx, Aug 05 '21 at 06:40

score 1 · Answer 1 · answered Aug 02 '21 at 13:00

This exactly matches the output and guarantees the ordering of the result set is ascending based on the Id column.

select id,
       min(case when Booked=1 then [Date] else null end) BookedYMIN,
       max(case when Booked=1 then [Date] else null end) BookedYMAX,
       min(case when Booked=0 then [Date] else null end) BookedNMIN,
       min(case when Booked=0 then [Date] else null end) BookedNMAX
from Bookings
group by id
order by id;

score 0 · Accepted Answer · edited Aug 03 '21 at 01:27

0

You can use conditional aggregation:

select id,
       min(case when booked = 1 then date end) as bookedymin,
       max(case when booked = 1 then date end) as bookedymax,
       min(case when booked = 0 then date end) as bookednmin,
       max(case when booked = 0 then date end) as bookednmax
from t
group by id

edited Aug 03 '21 at 01:27

sebk

117
7

answered Aug 02 '21 at 12:49

Gordon Linoff

1,242,037
58
646
786

@sebk . . . This answer was over 10 minutes before the other answer. – Gordon Linoff Aug 02 '21 at 18:04

Merging Rows and put values in different columns

2 Answers2