By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE #TEST (
[ID] [varchar](10) NULL,
[START_DATE] [date] NULL,
[END_DATE] [date] NULL,
[FEATURE] [int] NOT NULL
) ON [PRIMARY]
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)
8 rows affected
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;
| id | START_DATE | END_DATE | feature |
|---|---|---|---|
| 001 | 01/08/1995 00:00:00 | 31/12/1997 00:00:00 | 1 |
| 001 | 01/01/1998 00:00:00 | 31/03/2017 00:00:00 | 4 |
| 001 | 14/06/2000 00:00:00 | 31/03/2017 00:00:00 | 45 |
| 001 | 01/04/2013 00:00:00 | 31/03/2017 00:00:00 | 458 |
| 002 | 01/10/1929 00:00:00 | 25/05/2006 00:00:00 | 1 |
| 002 | 26/05/2006 00:00:00 | 10/11/2016 00:00:00 | 47 |
| 002 | 01/04/2013 00:00:00 | 10/11/2016 00:00:00 | 478 |