By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @masterTable TABLE (TrackID varchar(10), CountryFK INT, CRLFK INT, CorineLUFK int, SubTypeFK int, ProvidedFK int, GridTypeFK int, ResultFK int, ParamFK int)
INSERT INTO @masterTable VALUES
('FR_Soil', 11, 1, 35, 13, 155, 6, 1847, 6),
('FR_Soil', 11, 1, 35, 13, 155, 6, 17035, 8),
('FR_Soil', 11, 1, 35, 14, 155, 6, 37456, 9),
('FR_Soil', 11, 1, 35, 13, 170, 7, 5147, 10),
('FR_Soil', 11, 1, 35, 13, 170, 7, 32656, 14),
('IT_Soil', 12, 3, 14, 25, 143, 8, 2341, 4),
('IT_Soil', 12, 3, 14, 25, 143, 8, 2741, 8),
('IT_Soil', 12, 3, 14, 25, 143, 8, 2345, 7),
('IT_Soil', 12, 3, 14, 25, 143, 8, 229, 3),
('IT_Soil', 12, 3, 14, 25, 143, 8, 231, 9)
SELECT
TrackID, Property, Count(*) as PropertyValueCount
FROM (
SELECT DISTINCT TrackID, Property, PropertyValue
FROM
(
SELECT DISTINCT TrackID, CountryFK, CRLFK, CorineLUFK, SubTypeFK, ProvidedFK, GridTypeFK
FROM @masterTable
) m
UNPIVOT(
PropertyValue for Property IN (CountryFK, CRLFK, CorineLUFK, SubTypeFK, ProvidedFK, GridTypeFK)
) as UnP
) n
GROUP BY TrackID, Property
HAVING COUNT(*)>1
| TrackID | Property | PropertyValueCount |
|---|---|---|
| FR_Soil | GridTypeFK | 2 |
| FR_Soil | ProvidedFK | 2 |
| FR_Soil | SubTypeFK | 2 |