在以下解决方案中，我们将string_split与复制结合使用，以生成新记录。

select   dt        as date
        ,count(*)  as Count_unique_campaigns

from      
(
select    *
         ,dateadd(day, row_number() over(partition by Campaign_name  order by (select null))-1, Start_date) as dt
from     (
         select   *
         from     t
         outer apply string_split(replicate(',',datediff(day, Start_date, End_date)),',')
         ) t
) t
group by dt
order by dt

对于Azure和SQL Server 2022中的SQL，我们有一个基于[ordinal][4]的更干净的解决方案。
“Azure SQL数据库、Azure SQL托管示例和Azure Synapse Analytics（仅限无服务器SQL池）当前支持enable_ordinal参数和ordinal输出列。从SQL Server 2022（16.x）预览版开始，SQL Server中提供了参数和输出列。”

select   dt        as date
        ,count(*)  as Count_unique_campaigns

from      
(
select    *
         ,dateadd(day, ordinal-1, Start_date) as dt
from     (
         select   *
         from     t
         outer apply string_split(replicate(',',datediff(day, Start_date, End_date)),',', 1)
         ) t
) t
group by dt
order by dt

Fiddle升

你的样本数据似乎与你想要的结果不符，但我认为你想要的是：

DECLARE @Start date, @End date;

-- first, find the earliest and last date:

SELECT @Start = MIN([Start date]), @End = MAX([End date])
  FROM dbo.Campaigns;

-- now use a recursive CTE to build a date range, 
-- and count the number of campaigns that have a row
-- where the campaign was active on that date:

WITH d(d) AS 
(
  SELECT @Start 
  UNION ALL 
  SELECT DATEADD(DAY, 1, d) FROM d WHERE d < @End
)
SELECT 
  [Date] = d, 
  [Count unique campaigns] = COUNT(*) 
FROM d 
INNER JOIN dbo.Campaigns AS c
ON d.d >= c.[Start date] AND d.d <= c.[End date]
GROUP BY d.d OPTION (MAXRECURSION 32767);

this fiddle中的工作示例。