CODEWITHSUNDEEP
sqlsql-servermergeduplicates
skeletalmonkey
skeletalmonkey

Reputation: 736

Merge multiple rows in SQL with tie breaking on primary key

I have a table with data like the following

key | A | B    | C
---------------------------
 1  | x | 0    | 1
 2  | x | 2    | 0
 3  | x | NULL | 4
 4  | y | 7    | 1
 5  | y | 3    | NULL
 6  | z | NULL | 4

And I want to merge the rows together based on column A with largest primary key being the 'tie breaker' between values that are not NULL

Result
key | A | B    | C
---------------------------
 1  | x | 2    | 4
 2  | y | 3    | 1
 3  | z | NULL | 4

What would be the best way to achieve this assuming my data is actually 40 columns and 1 million rows with an unknown level of duplications?

Upvotes: 1

Views: 261

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Using ROW_NUMBER and conditional aggregation:

SQL Fiddle

WITH cte AS(
    SELECT *,
        rnB = ROW_NUMBER() OVER(PARTITION BY A ORDER BY CASE WHEN B IS NULL THEN 0 ELSE 1 END DESC, [key] DESC),
        rnC = ROW_NUMBER() OVER(PARTITION BY A ORDER BY CASE WHEN C IS NULL THEN 0 ELSE 1 END DESC, [key] DESC)
    FROM tbl
)
SELECT
    [key] = ROW_NUMBER() OVER(ORDER BY A),
    A,
    B = MAX(CASE WHEN rnB = 1 THEN B END),
    C = MAX(CASE WHEN rnC = 1 THEN C END)
FROM cte
GROUP BY A

Upvotes: 3

Related Questions