To get the desired output requires some data massaging, here is an example
😎
USE tempdb;
GO
SET NOCOUNT ON;
;WITH SAMPLE_DATA([Add], [New], [Pro], [Len], [Source]) AS
(
SELECT 'Acorn', Null, Null, Null, Null UNION ALL
SELECT Null, 'FC', Null, Null, Null UNION ALL
SELECT Null, Null, 'Day', Null, Null UNION ALL
SELECT Null, Null, Null, '2:00', Null UNION ALL
SELECT Null, Null, Null, Null, 'Phone' UNION ALL
SELECT Null, Null, Null, Null, 'Web' UNION ALL
SELECT 'Boster',Null, Null, Null, Null UNION ALL
SELECT Null, 'LC', Null, Null, Null UNION ALL
SELECT Null, Null, 'Night',Null, Null UNION ALL
SELECT Null, Null, Null, '1:00', Null UNION ALL
SELECT Null, Null, Null, Null, 'Phone' UNION ALL
SELECT Null, Null, Null, Null, 'Web'
)
,ORDERED_SET AS
(
SELECT
SD.[Add] AS SD_Add
,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS SD_RID
,SD.[New] AS SD_New
,SD.[Pro] AS SD_Pro
,SD.[Len] AS SD_Len
,SD.[Source] AS SD_Source
FROM SAMPLE_DATA SD
)
,GROUPS_IN_SET AS
(
SELECT
OS.SD_Add
,ROW_NUMBER() OVER (ORDER BY OS.SD_RID) AS GR_RID
,OS.SD_RID
FROM ORDERED_SET OS
WHERE OS.SD_Add IS NOT NULL
)
,MARKED_GROUPS AS
(
SELECT
GIS.SD_Add AS GROUP_NAME
,GIS.GR_RID AS GROUP_NUMBER
,GIS.SD_RID AS GROUP_START
,ISNULL(GTO.SD_RID - 1,(SELECT MAX(SD_RID) FROM ORDERED_SET)) AS GROUP_END
FROM GROUPS_IN_SET GIS
LEFT OUTER JOIN GROUPS_IN_SET GTO
ON GIS.GR_RID = GTO.GR_RID -1
)
,SUB_GROUPS AS
(
SELECT
MG.GROUP_NAME
,OS.SD_Source
FROM ORDERED_SET OS
CROSS JOIN MARKED_GROUPS MG
WHERE OS.SD_RID BETWEEN MG.GROUP_START AND MG.GROUP_END
AND OS.SD_Source IS NOT NULL
)
SELECT
MG.GROUP_NAME
,MG.GROUP_NUMBER
,SG.SD_Source
,MAX(OS.SD_New ) AS SD_New
,MAX(OS.SD_Pro ) AS SD_Pro
,MAX(OS.SD_Len ) AS SD_Len
FROM ORDERED_SET OS
CROSS JOIN MARKED_GROUPS MG
INNER JOIN SUB_GROUPS SG
ON MG.GROUP_NAME = SG.GROUP_NAME
WHERE OS.SD_RID BETWEEN MG.GROUP_START AND MG.GROUP_END
AND OS.SD_Source IS NULL
GROUP BY MG.GROUP_NAME,MG.GROUP_NUMBER,SG.SD_Source;
Results
GROUP_NAME GROUP_NUMBER SD_Source SD_New SD_Pro SD_Len
---------- -------------------- --------- ------ ------ ------
Acorn 1 Phone FC Day 2:00
Acorn 1 Web FC Day 2:00
Boster 2 Phone LC Night 1:00
Boster 2 Web LC Night 1:00