Combining Two SQL Tables with Common ID

In this article, we will explore how to combine two SQL tables based on a common ID. The goal is to retrieve the desired data in a single row instead of multiple rows.

Introduction

Many applications involve combining data from multiple tables to create a cohesive view. In this case, we have two tables: Address and Contact. Both tables share a common ID called LinkID, which we will use as the basis for our combination.

The Address table contains information about physical addresses, while the Contact table stores details about individuals or entities that are associated with these addresses. We want to combine this data into a single result set where each row represents an address and its corresponding contact information.

Using Row Numbers and Conditional Aggregates

To achieve this combination, we will use two SQL techniques:

1. ROW_NUMBER(): This function assigns a unique sequence number to each row within a partition of a result set. In our case, we will use it to assign an ordinal position to each contact within the Contact table.
2. MAX(CASE): This is a conditional aggregate that returns the maximum value from a set of cases. We will use it to build up a pivoted result.

SQL Code

Below is the SQL code that combines the two tables:

-- Declare temporary tables for Address and Contact
DECLARE @Address TABLE(LinkID INT, Address1 VARCHAR(100))
INSERT @Address
VALUES 
    (1, '1234 Homeless Street'),
    (2, '115 N Turner Drive'),
    (3, '1213 Riverside Avenue'),
    (4, '2015 Shorewood Drive')

DECLARE @Contact TABLE(LinkID INT, Contact_c VARCHAR(100), Relation_c VARCHAR(100), Enddate_d DATE)
INSERT @Contact
VALUES 
    (1, 'ARNOLD COLLINS', 'FATHER', NULL),
    (1, 'PAMELA COLLINS', 'MOTHER', NULL),
    (2, 'LUCY HOWARD', 'MOTHER', NULL),
    (2, 'LEROY SHEPHARD', 'NEIGH', '2022-01-01'),
    (3, 'ROGER RUSELL', 'FATHER', NULL),
    (3, 'AMY RUSELL', 'MOTHER', NULL),
    (4, 'JACKIE BARD', 'MOTHER', NULL),
    (4, 'JASON BIEL', 'FRIEND', NULL)

-- Create a CTE to assign ordinal position to contacts
WITH Contacts_CTE AS (
    SELECT *, Ordinal = ROW_NUMBER() OVER(PARTITION BY LinkID ORDER BY Relation_c)
    FROM @Contact
    WHERE Enddate_d IS NULL
)
SELECT
    A.LinkID,
    Contact1  = MAX(CASE WHEN C.Ordinal = 1 THEN C.Contact_c END),
    Relation1 = MAX(CASE WHEN C Ordinal = 1 THEN C.Relation_c END),
    Contact2  = MAX(CASE WHEN C Ordinal = 2 THEN C.Contact_c END),
    Relation2 = MAX(CASE WHEN C.Ordinal = 2 THEN C.Relation_c END),
    A.Address1
FROM @Address A
LEFT JOIN Contacts_CTE C ON C.LinkID = A.LinkID
GROUP BY A.LinkID, A.Address1
ORDER BY A.LinkID

Results

The resulting table has the combined contact information for each address:

Using PIVOT

While the above solution works well, it has limitations when dealing with multiple values per row. In such cases, we can use the PIVOT clause in SQL Server.

However, PIVOT has some restrictions and might not be suitable for all scenarios, especially when dealing with multiple values or dynamic sets of values. The solution using conditional aggregates is generally more flexible and powerful when working with variable numbers of columns.

Conclusion

In this article, we demonstrated how to combine two SQL tables based on a common ID using ROW_NUMBER() and MAX(CASE) functions. This technique provides a flexible way to build up a pivoted result without relying on fixed sets of values or the limitations of the PIVOT clause.

While other methods like PIVOT can be used, they have their own set of restrictions and might not be suitable for every scenario. The combination using conditional aggregates offers more flexibility and is generally a better approach when dealing with variable numbers of columns or multiple values per row.

Last modified on 2025-01-26