SQL Server – How to Convert Left Join Resulting in Multiple Rows to Separate Columns

casejoin;sql serversql-server-2012

I need some logic help with a complex join situation. Here is an overall idea of the 3 tables I am working with

Table 1:

ID Number Type 1
A1
A2

Table 2:

ID Number Type 1 ID Number Type 2
A1 B1
A1 B2
A2 B3
A2 B4

Table 3:

ID Number Type 2 Designation
B1 D 1
B2 D 2
B3 D 1
B4 D 2

I am trying to get this result:

ID Number Type 1 ID Number Type 2 for D 1 ID Number Type 2 for D 2
A1 B1 B3
A2 B2 B4

Basically table 2 results in multiple rows, and I want these multiple resulting ID Type 2’s to show in two separate columns in the resulting table, based on the designation that comes from a third table.

Currently, I have table 1 and table 2, as well as table 2 and table 3, joined with left joins. I am using Case When logic, but it is only resulting in one of the two designation columns getting filled for obvious reasons. Here is my current code:

Case when (Designation = D1) THEN (ID Type 2) END AS [D1]

Case when (Designation = D2) THEN (ID Type 2) END AS [D2]

Can anyone help improve my logic? thank you in advance!

Best Answer

Your "Table 2" mapping won't help you for this task, because it holds non-useful association between A values and B values (A1 is matched with B1 and B2. A2 is matched with B3 and B4).

What you can do here is generate an ad-hoc remapping with ranking values using three window functions:

  • ROW_NUMBER on table 1, to identify the order between A1 and A2
  • DENSE_RANK on table 3, ordering by Designation, to identify the order between designation values

These two will help you generate the association you need: they'll be useful for your join condition betweeen table 1 and 3.

  • ROW_NUMBER on table 3, partitioning by Designation and ordering by ID2, to identify the ordering of rows for each D1 and D2 designations

This one will make you pivot work correctly, selecting the right values you need for each column.

WITH t1 AS (
    SELECT *, 
           ROW_NUMBER() OVER(ORDER BY [ID Number Type 1]) AS rn1
    FROM tab1
), t3 AS (
    SELECT *, 
           ROW_NUMBER() OVER(PARTITION BY Designation 
                             ORDER     BY [ID Number Type 2]) AS rn,
           DENSE_RANK() OVER(ORDER BY Designation) AS rn3
    FROM tab3
) 
SELECT [ID Number Type 1],
       MAX(CASE WHEN rn = 1 THEN [ID Number Type 2] END) AS [ID Number Type 2 for D 1], 
       MAX(CASE WHEN rn = 2 THEN [ID Number Type 2] END) AS [ID Number Type 2 for D 2]
FROM       t1
INNER JOIN t3
        ON t1.rn1 = t3.rn3
GROUP BY [ID Number Type 1]

Check the demo here.

Related Question