How to Group Similar Rows in Sql?

Question

DATE	ADRESS
2010-09-02	ENGLAND
2010-09-03	ENGLAND
2010-09-04	ENGLAND
2010-09-05	ENGLAND
2010-09-06	GERMANY
2010-09-07	GERMANY
2010-09-08	GERMANY
2011-09-03	ENGLAND
2012-09-02	ENGLAND
2013-09-03	ENGLAND

I have a table like this and what I want to do, I want the result I want to get, how can I do it?

DATE	ADRESS
2010-09-02 - 2010-09-05	ENGLAND
2010-09-06 - 2010-09-08	GERMANY
2011-09-03 - 2013-09-03	ENGLAND

Answer 1

You have a gaps and islands problem here, you could use the difference between two row_numbers approach to solving it, try the following:

select concat_ws(' - ', min([date]), max([date])) as [date],
       address
from
(
  select *,
   row_number() over (order by date) -
   row_number() over (partition by address order by [date]) grp
  from table_name
) t
group by address, grp
order by min([date])

see demo