Adnan Yaseen Adnan Yaseen - 6 months ago 13
SQL Question

Show duplicates values in Left outer join once

There are plenty questions on SO regarding how to remove the duplicate records in the left outer join. I have a simple left outer join like this between two tables Table1 and Table2 where Table1 has one to many relationship with Table2 (I have directly written this simple query here just to explain what I am after and it is not the actual query),

SELECT a.ID, a.Name, b.Value
FROM Table1 a
LEFT OUTER JOIN Table2 b ON a.ID == b.Table1_ID


Now this returns the result like this,

ID Name Value
1 Test1 TestValue1
1 Test1 TestValue2
1 Test1 TestValue3
1 Test1 NULL
2 Test2 TestValue4
2 Test2 NULL
2 Test2 TestValue5


Now this output is correct and I understand that it is a correct behavior. But is there some way by which I can get the following output. This might be simple but I have not done this before and everytime I search for the solution I came across the question and threads asking to remove the duplicates. I don't want to remove the duplicates. Just want to show the values from Table1 just once like below,

ID Name Value
1 Test1 TestValue1
TestValue2
TestValue3
NULL
2 Test2 TestValue4
NULL
TestValue5

Answer

You can use ROW_NUMBER to determine the first row among related values:

WITH CTE AS(
    SELECT 
        a.ID, 
        a.Name, 
        b.Value,
        Rn = ROW_NUMBER() OVER(PARTITION BY a.ID ORDER BY (SELECT NULL))
    FROM Table1 a 
    LEFT JOIN Table2 b 
        ON a.ID = b.Table1_ID
)
SELECT
    ID  = CASE WHEN Rn = 1 THEN CONVERT(VARCHAR(10), a.ID) ELSE '' END),
    Name = CASE WHEN Rn = 1 THEN Name ELSE '' END),
    Value
FROM CTE

Replace ORDER BY (SELECT NULL)) with the column you want the ROW_NUMBER to be based from.

Comments