How to add an column to the same table making the 2 columns in the table unique (query)-CodePudding

I have an table with the following:

Name	NamePartner	DateStartCustomer	DateCustomerEnd
123	356	01-01-1990	NULL
356	123	01-01-1990	NULL
567	NULL	01-01-2020	NULL

What I would like to want is an query to add an extra column in this table were I can say that the if Name in the first column is also in the column of NamePartner and the DateCustomerEnd is NULL --> so ongoing relation, then those rows are 1 household. So row 1 and row 2 should be 1 household. I want to give the value of the first columns to that new column

Example:

Name	NamePartner	UniqueHousehold	DateStartCustomer	DateCustomerEnd
123	356	123	01-01-1990	NULL
356	123	123	01-01-1990	NULL
567	NULL	567	01-01-2020	NULL

CodePudding user response：

The query below will give you the desired result set.

To accomplish this I used ROW_NUMBER to generate a unique ID for each row.
This is needed to establish a unique household. Based on your example it appears you want whatever record is first to be the household value. Ideally your source table would have an ID column or primary key to use for this.

DROP TABLE IF EXISTS #Customer;
CREATE TABLE #Customer
(
    Name INT
    , NamePartner INT
    , DateStartCustomer DATETIME
    , DateCustomerEnd DATETIME
);

INSERT INTO #Customer
VALUES
    (123, 356, '01/01/1990', NULL)
    , (356, 123, '01/01/1990', NULL)
    , (567, NULL, '01/01/2020', NULL);

WITH RowNum AS
(
    SELECT *, ROW_NUMBER() OVER(ORDER BY Name) AS Id
    FROM #Customer
)

SELECT
    C.Name
    , C.NamePartner
    , COALESCE(HH.Name, C.Name) AS UniqueHousehold
    , C.DateStartCustomer
    , C.DateCustomerEnd
FROM
    RowNum           AS C
    LEFT JOIN RowNum AS HH
        ON C.NamePartner = HH.Name
           AND  C.Id > HH.Id;

DROP TABLE IF EXISTS #Customer;