Create new date ranges from overlapping date ranges and assign an ID

I have the following table

ID  | START_DATE | END_DATE   | FEATURE
---------------------------------------
001 | 1995-08-01 | 1997-12-31 | 1
001 | 1998-01-01 | 2017-03-31 | 4
001 | 2000-06-14 | 2017-03-31 | 5
001 | 2013-04-01 | 2017-03-31 | 8
002 | 1929-10-01 | 2006-05-25 | 1
002 | 2006-05-26 | 2016-11-10 | 4
002 | 2006-05-26 | 2016-11-10 | 7
002 | 2013-04-01 | 2016-11-10 | 8

I want to convert this table into a consolidated table which will look for overlapping date ranges and then combine these into new rows. Creating a non-overlapping set of date ranges.

The bit that I need the most help with is the consolidations of the ‘feature’ column which will concatenate each feature into the format below.

ID  | START_DATE | END_DATE   | FEATURE
---------------------------------------
001 | 1995-08-01 | 1997-12-31 | 1
001 | 1998-01-01 | 2000-06-13 | 4
001 | 2000-06-14 | 2013-03-31 | 45
001 | 2013-04-01 | 2017-03-31 | 458
002 | 1929-10-01 | 2006-05-25 | 1
002 | 2006-05-26 | 2013-03-31 | 47
002 | 2013-04-01 | 2016-11-10 | 478

I’ve used the following to create the test data.

CREATE TABLE #TEST (
    [ID] [varchar](10) NULL,
    [START_DATE] [date] NULL,
    [END_DATE] [date] NULL,
    [FEATURE] [int] NOT NULL
) ON [PRIMARY]
GO


INSERT INTO #TEST

VALUES

('001','1998-01-01','2017-03-31',4),
('001','2000-06-14','2017-03-31',5),
('001','2013-04-01','2017-03-31',8),
('001','1995-08-01','1997-12-31',1),
('002','2006-05-26','2016-11-10',4),
('002','2006-05-26','2016-11-10',7),
('002','2013-04-01','2016-11-10',8),
('002','1929-10-01','2006-05-25',1)

Answer

You can use apply :

select distinct t.id, t.START_DATE, t.END_DATE, coalesce(tt.feature, t.feature) as feature
from #test t outer apply
     ( select ' '+t1.feature
       from #test t1 
       where t1.id = t.id and t1.end_date = t.end_date and t1.start_date <= t.start_date
       order by t1.start_date
       for xml path('')
     ) tt(feature)
order by t.id, t.START_DATE;

Here is a db<>fiddle.