按位列分組

我想要按具有活動列的行分組，如結果

我用這個程式碼托盤，我沒有得到結果

   WITH CTE as (
 SELECT
   RN = ROW_NUMBER() OVER (ORDER BY id),
   *
 FROM test
)
SELECT

convert(nvarchar(50),[Current Row].datetime,120) + ' To ' +  convert(nvarchar(50),[Next Row].datetime,120)

FROM CTE [Current Row]
LEFT JOIN CTE [Previous Row] ON
 [Previous Row].RN = [Current Row].RN - 1
and (ISNULL( [Previous Row].active,0)=0) and [Current Row].active=1
RIGHT JOIN CTE [Next Row] ON
 [Next Row].RN = [Current Row].RN + 1 
and [Current Row].active=1 and isnull([Next Row].active,0)=0

得到這樣的結果

我希望按活動和日期分組，如最佳結果

這是“鴻溝和孤島”的任務。

WITH cte AS (
   SELECT *, 
          ROW_NUMBER() OVER (ORDER BY date) - ROW_NUMBER() OVER (PARTITION BY active ORDER BY date) [Group]
   FROM test
)
SELECT MIN(date) [From],
      MAX(date) [Till],
      COUNT(*) [Count]
FROM cte
WHERE active = 1
GROUP BY [Group]
ORDER BY [From]

小提琴（有一些解釋）。

引用自：https://dba.stackexchange.com/questions/297771