@Everybody,
I would like to thank you all for your assistance. Being a junior in SQL, this was a bit out of my league.

I shall include my solution...

@Lynn Pettis
Apologies for any inconvenience!
Let's get it right this time...

USE [DBNAME HERE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Hello Mike and thank you for your time.
I have attached an excel file with some data and the expected result.

Every help is highly appreciated.🙂