Concatenate more than two tables horizontally in SQL Server

Question

The Following is the schema

+---------+---------+
| Employee Table    |
+---------+---------+
| EmpId   | Name    | 
+---------+---------+
| 1       | John    |
| 2       | Lisa    |
| 3       | Mike    |
|         |         |
+---------+---------+

+---------+-----------------+
| Family   Table            |
+---------+-----------------+
| EmpId   | Relationship    | 
+---------+-----------------+
| 1       | Father          |
| 1       | Mother          |
| 1       | Wife            |
| 2       | Husband         |
| 2       | Child           |
+---------+-----------------+

+---------+---------+
| Loan  Table       |
+---------+--------+
| LoanId  | EmpId  | 
+---------+--------+
| L1      | 1      |
| L2      | 1      |
| L3      | 2      |
| L4      | 2      |
| L5      | 3      |
+---------+--------+

Employee Table and Family Table has one to many relationship
Employee Table and Loan Table has one many Relation ship

I have tried Joins but it gives redundant rows.

Now the desired output will be

+---------+---------+--------------+---------+
| EmpId   | Name    | RelationShip | Loan    | 
+---------+---------+--------------+---------+
| 1       | John    | Father       | L1      |
| -       | -       | Mother       | L2      |
| -       | -       | Wife         | -       |
| 2       | Lisa    | Husband      | L3      |
| -       | -       | Child        | L4      |
| 3       | Mike    | -            | L5      |
|         |         |              |         |
+---------+---------+--------------+---------+

The loans belong to employee (1:N) and Family relationships also belong to Employee (1:N). How can you attribute loans L2 and L4 to non-employees - this information isn't contained in the data? — StuartLC, Dec 27 '14 at 11:39
A select has no set order and there is not sort to produce this. There is no way to do this with the existing columns. — paparazzo, Dec 27 '14 at 12:15

Gordon Linoff · Accepted Answer · 2014-12-28T19:51:48.337

It looks like you are trying to assign the loans "sequentially" to rows in the family table. The approach to solve this is to first get the right rows, and then to get the loans assigned to rows.

The right rows (and first three columns) are:

select f.EmpId, e.Name, f.Relationship
from family f join
     Employee e
     on f.empid = e.empid;

Note that this does not put hyphens in the columns for repeated values, it puts in the actual values. Although you can arrange for the hyphens in SQL, it is a bad idea. SQL results are in the form of tables, which are unordered sets with values for each column and each row. When you start putting hyphens in, you are depending on the order.

Now the problem is joining in the loans. This is actually pretty easy, by using row_number() to add a join key:

select f.EmpId, e.Name, f.Relationship, l.LoanId
from Employee e left join
     (select f.*, row_number() over (partition by f.EmpId order by (select NULL)) as seqnum
      from family f
     ) f 
     on f.empid = e.empid left join
     (select l.*, row_number() over (partition by l.EmpId order by (select NULL)) as seqnum
      from Loan l
     ) l
     on f.EmpId = l.EmpId and f.seqnum = l.seqnum;

Note that this does not guarantee the order of assignment of loans for a given employee. Your data does not seem to have enough information to handle a more consistent assignment.

@VladimirBaranov . . . It should now. I was starting the chain of `left join`s with the wrong table. — Gordon Linoff, Dec 28 '14 at 19:52

score 3 · Answer 2 · edited Nov 13 '18 at 23:13

The approach outlined below allows to easily "concatenate" more tables to the result set. It is not limited to two tables.

I'll use table variables to illustrate the solution. In real life these tables would be real tables, of course, not variables, but I'll stick with variables to make this sample script easy to run and try.

declare @TEmployee table (EmpId int, Name varchar(50));
declare @TFamily table (EmpId int, Relationship varchar(50));
declare @TLoan table (EmpId int, LoanId varchar(50));

insert into @TEmployee values (1, 'John');
insert into @TEmployee values (2, 'Lisa');
insert into @TEmployee values (3, 'Mike');

insert into @TFamily values (1, 'Father');
insert into @TFamily values (1, 'Mother');
insert into @TFamily values (1, 'Wife');
insert into @TFamily values (2, 'Husband');
insert into @TFamily values (2, 'Child');

insert into @TLoan values (1, 'L1');
insert into @TLoan values (1, 'L2');
insert into @TLoan values (2, 'L3');
insert into @TLoan values (2, 'L4');
insert into @TLoan values (3, 'L5');

We'll need a table of numbers.

SQL, Auxiliary table of numbers

http://web.archive.org/web/20150411042510/http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

http://dataeducation.com/you-require-a-numbers-table/

Again, in real life you'll have a proper table of numbers, but for this example I'll use the following:

declare @TNumbers table (Number int);
insert into @TNumbers values (1);
insert into @TNumbers values (2);
insert into @TNumbers values (3);
insert into @TNumbers values (4);
insert into @TNumbers values (5);

The main idea behind my approach is to make a helper table that would contain correct number of rows for each EmpId at first and then use this table to get results efficiently.

We'll start with counting number of relationships and loans for each EmpId:

WITH
CTE_Rows
AS
(
    SELECT Relationships.EmpId, COUNT(*) AS EmpRows
    FROM @TFamily AS Relationships
    GROUP BY Relationships.EmpId

    UNION ALL

    SELECT Loans.EmpId, COUNT(*) AS EmpRows
    FROM @TLoan AS Loans
    GROUP BY Loans.EmpId
)

Then we calculate the maximum number of rows for each EmpId:

,CTE_MaxRows
AS
(
    SELECT
        CTE_Rows.empid
        ,MAX(CTE_Rows.EmpRows) AS MaxEmpRows
    FROM CTE_Rows
    GROUP BY CTE_Rows.empid
)

The CTE above has one row for each EmpId: EmpId itself and a maximum number of relationships or loans for this EmpId. Now we need to expand this table and generate the given number of rows for each EmpId. Here I'm using the Numbers table for it:

,CTE_RowNumbers
AS
(
SELECT
    CTE_MaxRows.empid
    ,Numbers.Number AS rn
FROM
    CTE_MaxRows
    CROSS JOIN @TNumbers AS Numbers
WHERE
    Numbers.Number <= CTE_MaxRows.MaxEmpRows
)

Then we need to add row numbers to all tables with data, which we'll use for joining later. You can order the row numbers using other columns in your tables. For this example there is not much choice.

,CTE_Relationships
AS
(
    SELECT
        Relationships.EmpId
        ,ROW_NUMBER() OVER (PARTITION BY Relationships.EmpId ORDER BY Relationships.Relationship) AS rn
        ,Relationships.Relationship
    FROM @TFamily AS Relationships
)
,CTE_Loans
AS
(
    SELECT
        Loans.EmpId
        ,ROW_NUMBER() OVER (PARTITION BY Loans.EmpId ORDER BY Loans.LoanId) AS rn
        ,Loans.LoanId
    FROM @TLoan AS Loans
)

Now we are ready to join all this together. CTE_RowNumbers has exact number of rows that we need, so simple LEFT JOIN is enough:

,CTE_Data
AS
(
    SELECT
        CTE_RowNumbers.empid
        ,CTE_Relationships.Relationship
        ,CTE_Loans.LoanId
    FROM
        CTE_RowNumbers
        LEFT JOIN CTE_Relationships ON CTE_Relationships.EmpId = CTE_RowNumbers.EmpId AND CTE_Relationships.rn = CTE_RowNumbers.rn
        LEFT JOIN CTE_Loans ON CTE_Loans.EmpId = CTE_RowNumbers.EmpId AND CTE_Loans.rn = CTE_RowNumbers.rn
)

We are almost done. It is possible that the main Employee table has some EmpIds that don't have any related data, like EmpId = 3 in your sample data. To get these EmpIds in the result set I'll left join the CTE_Data to the main table and replace NULLs with dashes:

SELECT
    Employees.EmpId
    ,Employees.Name
    ,ISNULL(CTE_Data.Relationship, '-') AS Relationship
    ,ISNULL(CTE_Data.LoanId, '-') AS LoanId
FROM
    @TEmployee AS Employees
    LEFT JOIN CTE_Data ON CTE_Data.EmpId = Employees.EmpId
ORDER BY Employees.EmpId, Relationship, LoanId;

To get the full script just put all code blocks from this post together in the same order as they appear here.

This is the result set:

EmpId   Name   Relationship   LoanId
1       John   Father         L1
1       John   Mother         L2
1       John   Wife           -
2       Lisa   Child          L3
2       Lisa   Husband        L4
3       Mike   -              L5

score 0 · Answer 3 · answered Dec 28 '14 at 12:38

Vladimir Baranov already wrote a good solution, but it is rather long (and has a small issue: you wanted Husband-L3 and Child-L4, but this solution returns Child-L3 and Husband-L4).

Gordon Linoff wrote a shorter solution, but it is not working correctly.

I could fix Gordon's solution, like this:

SELECT e.EmpId, e.Name, f.Relationship, l.LoanId
FROM @TEmployee e
LEFT JOIN (
    SELECT f.*, ROW_NUMBER() OVER (PARTITION BY f.EmpId ORDER BY (SELECT NULL)) AS seqnum
    FROM @TFamily f
) f ON f.empid = e.empid 
LEFT JOIN (
    SELECT l.*, ROW_NUMBER() OVER (PARTITION BY l.EmpId ORDER BY (SELECT NULL)) AS seqnum
    FROM @TLoan l
) l ON l.EmpId = e.EmpId AND (f.seqnum = l.seqnum OR f.seqnum IS NULL)

However, I would rather say that the question is incorrect, because it asks us to match family members to specific loans arbitrarily (when no real relationship exists).

I would rather say that the correct question is the one with the following answer:

SELECT e.EmpId, e.Name,
    SUBSTRING((
        SELECT ', '+f.Relationship AS '*'
        FROM @TFamily f
        WHERE f.EmpId=e.EmpId
        FOR XML PATH(''), TYPE
    ).value('.','nvarchar(4000)'),3,4000) AS FamilyMembers,
    SUBSTRING((
        SELECT ', '+l.LoanId AS '*'
        FROM @TLoan l
        WHERE l.EmpId=e.EmpId
        FOR XML PATH(''), TYPE
    ).value('.','nvarchar(4000)'),3,4000) AS Loans
FROM @TEmployee e

Concatenate more than two tables horizontally in SQL Server

3 Answers3

Linked