SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

DECLARE @t TABLE (
    HouseID INT,
    FirstName NVARCHAR(64),
    LastName NVARCHAR(64),
    EventType NVARCHAR(64),
    EventDate DATE
);

INSERT INTO @t (HouseID, FirstName, LastName, EventType, EventDate)
VALUES
    (1, 'Will', 'Stanton', 'Birthday', '1974-01-05'),
    (1, 'Mary', 'Stanton', 'Birthday', '1980-05-22'),
    (2, 'Jason', 'Stockmore', 'Birthday', '1987-12-07'),
    (3, 'Mark', 'Mellony', 'Wedding', '2021-04-04'),
    (3, 'Stacy', 'Mellony', 'Wedding', '2021-04-04'),
    (4, 'Stephen', 'Johnson', 'Wedding', '2012-01-30'),
    (4, 'Janetsy', 'Johnson', 'Wedding', '2012-01-30'),
    (5, 'George', 'Jackson', 'Wedding', '2009-11-15'),
    (5, 'Sally', 'Mistmoore', 'Wedding', '2009-11-15'),
    (6, 'Sandy', 'Katz', 'Wedding', '2010-03-19'),
    (6, 'Jeff', 'Trilov', 'Wedding', '2016-09-09'),
    (7, 'Sandra', 'Kirchbaum', 'Wedding', '2011-05-22'),
    (8, 'Jessica', 'Bower', 'Birthday', '1996-02-26'),
    (8, 'Frank', 'Fjorn', 'Birthday', '1969-07-19');

;WITH
[DoubleWeddings] AS (
SELECT
            [HouseID]
    FROM
            @t
    WHERE
            [EventType] = 'Wedding'
    GROUP BY
            [HouseID],
            [EventDate]
        HAVING
            COUNT(*) = 2
),
[DoubleWeddingsSameLastName] AS (
SELECT
            T.[HouseID]
    FROM
            [DoubleWeddings] DW
        JOIN
            @t T
                ON T.[HouseID] = DW.[HouseID]
    GROUP BY
            T.[HouseID],
            T.[LastName]
        HAVING
            COUNT(*) = 2
),
[DoubleWeddingsDifferentLastName] AS (
SELECT [HouseID] FROM [DoubleWeddings]
EXCEPT
SELECT [HouseID] FROM [DoubleWeddingsSameLastName]
),
[Couples] AS ( 
SELECT
           T.[HouseID],
           ROW_NUMBER() OVER (PARTITION BY T.[HouseID] ORDER BY 1/0) [RN],
           T.[FirstName],
           T.[LastName],
           T.[EventType],
           T.[EventDate]
    FROM
           [DoubleWeddings] DW
        JOIN
           @t T
               ON T.[HouseID] = DW.[HouseID]
)
SELECT
            DWSL.[HouseID],
            FORMATMESSAGE(
                '%s and %s %s',
                F.[FirstName],
                S.[FirstName],
                S.[LastName]) [FinalName],
            F.[EventType],
            F.[EventDate]
    FROM
            [DoubleWeddingsSameLastName] DWSL
        JOIN
            [Couples] F
                ON F.[HouseID] = DWSL.[HouseID] AND F.[RN] = 1
        JOIN
            [Couples] S
                ON S.[HouseID] = DWSL.[HouseID] AND S.[RN] = 2
UNION ALL
SELECT
            DWDL.[HouseID],
            FORMATMESSAGE(
                '%s %s and %s %s',
                F.[FirstName],
                F.[LastName],
                S.[FirstName],
                S.[LastName]) [FinalName],
            F.[EventType],
            F.[EventDate]
    FROM
            [DoubleWeddingsDifferentLastName] DWDL
        JOIN
            [Couples] F
                ON F.[HouseID] = DWDL.[HouseID] AND F.[RN] = 1
        JOIN
            [Couples] S
                ON S.[HouseID] = DWDL.[HouseID] AND S.[RN] = 2
UNION ALL
SELECT
            T.[HouseID],
            FORMATMESSAGE(
                '%s %s',
                T.[FirstName],
                T.[LastName]) [FinalName],
            T.[EventType],
            T.[EventDate]
    FROM
            @t T
        LEFT JOIN
            [DoubleWeddings] DW
                ON DW.[HouseID] = T.[HouseID]
        WHERE
            DW.[HouseID] IS NULL
    ORDER BY
          [HouseID],
          [EventDate],
          [FinalName]

HouseID	FinalName	EventType	EventDate
1	Will Stanton	Birthday	1974-01-05
1	Mary Stanton	Birthday	1980-05-22
2	Jason Stockmore	Birthday	1987-12-07
3	Mark and Stacy Mellony	Wedding	2021-04-04
4	Stephen and Janetsy Johnson	Wedding	2012-01-30
5	George Jackson and Sally Mistmoore	Wedding	2009-11-15
6	Sandy Katz	Wedding	2010-03-19
6	Jeff Trilov	Wedding	2016-09-09
7	Sandra Kirchbaum	Wedding	2011-05-22
8	Frank Fjorn	Birthday	1969-07-19
8	Jessica Bower	Birthday	1996-02-26