Try this. I get the results that you want, but you should try it in your system to check the performance.

with ct1

as

(

select t.ID,t.Date, case when t.Column1 is ...

with Mycte

as

(

select ROW_NUMBER()over(order by id)as c, t.* from ##TEST as t

)

select t1.DT as "startDate", t2.DT as "endDate"from Mycte as t1

left join Mycte as t2

on t1.ID =t2.ID and t1.c=t2.c-1

SELECT

COALESCE(

STUFF(

(SELECT ',' + CAST(dbs AS VARCHAR(10)) AS [text()]

FROM #l

ORDER BY dbs

FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),

1, 1, ''),

'') AS letters

FROM #l as c;

select customerId, window, product, sales,

( Case when count(sales) over(partition by customerid order by customerid) >0 then 1 else 0 end)as class

from #example