tmajor99
asked on
MS SQL - Duplicate values across columns
How can I find duplicate values across columns? I have a table with 10 columns contains ID's and I believe there are some duplicate values across these 10 columns. How can I create a MS SQL that will identify duplicates across columns?
ID Part1 Part2 Part3 Part4 Part5 Part6 Part7 Part8 Part9 Part10
100 1 90 30 90
101 98
102 301 821 400 399 400 543 912 832 788 301
103 943 981 312
Expected Results:
ID Duplicate Values
100 90
102 301, 400
Again, I am looking for duplicate values within the same row across multiple columns.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SELECT d.ID, ca1.*
FROM #data d
CROSS APPLY (
SELECT STUFF(
MAX(CASE WHEN row_num = 1 THEN ', ' + CAST(Part AS varchar(10)) ELSE '' END) +
MAX(CASE WHEN row_num = 2 THEN ', ' + CAST(Part AS varchar(10)) ELSE '' END) +
MAX(CASE WHEN row_num = 3 THEN ', ' + CAST(Part AS varchar(10)) ELSE '' END) +
MAX(CASE WHEN row_num = 4 THEN ', ' + CAST(Part AS varchar(10)) ELSE '' END) +
MAX(CASE WHEN row_num = 5 THEN ', ' + CAST(Part AS varchar(10)) ELSE '' END) +
MAX(CASE WHEN row_num = 6 THEN ', ' + CAST(Part AS varchar(10)) ELSE '' END) +
MAX(CASE WHEN row_num = 7 THEN ', ' + CAST(Part AS varchar(10)) ELSE '' END) +
MAX(CASE WHEN row_num = 8 THEN ', ' + CAST(Part AS varchar(10)) ELSE '' END) +
MAX(CASE WHEN row_num = 9 THEN ', ' + CAST(Part AS varchar(10)) ELSE '' END) +
MAX(CASE WHEN row_num =10 THEN ', ' + CAST(Part AS varchar(10)) ELSE '' END)
, 1, 2, '') AS dup_parts
FROM (
SELECT ID, Part, ROW_NUMBER() OVER(ORDER BY Part) AS row_num
FROM ( VALUES(Part1), (Part2), (Part3), (Part4), (Part5), (Part6), (Part7), (Part8), (Part9), (Part10) ) AS data(part)
WHERE Part IS NOT NULL
GROUP BY Part
HAVING COUNT(*) > 1
) AS derived
) AS ca1
WHERE ca1.dup_parts IS NOT NULL