I am using SQL Server 2008R2 and have tableA that has four columns res_id,res_id2,res_id3,res_id4 numeric. I want to find away to find the occurrences of the same IDs on each row (the met column) excluding 0 or null
Example:
golf_id res_id res_id2 res_id3 res_id4 met
1579 2068252 2068252 NULL 0 1
1492 2076015 2076015 2076016 2076016 2
1494 2076046 2076046 2076046 2076047 2
1617 2077041 2077042 2077043 2077044 4
1545 2076102 2076102 NULL NULL 1
So in the first row I have only 2068252 so met should be 1
In the second row I have 2076015 and 2076016 so met should be 2
In the third row I have 2076046 and 2076047 so met should be 2
In the fourth row I have 2077041, 2077042, 2077043, 2077044 so met should be 4
In the fifth row I have 2076102 so met should be 1
Thank you
A simple solution would be to unpivot your data, and then COUNT the DISTINCT values. I'm pretty sure this'll work on 2008 R2 (though I don't have access to such an instance, nor have had access to one for the best part of a decade).
WITH YourTable AS(
SELECT *
FROM (VALUES(1579,2068252,2068252,NULL ,0 ),
(1492,2076015,2076015,2076016,2076016),
(1494,2076046,2076046,2076046,2076047),
(1617,2077041,2077042,2077043,2077044),
(1545,2076102,2076102,NULL ,NULL ))V(golf_id,res_id,res_id2,res_id3,res_id4))
SELECT YT.golf_id,
YT.res_id,
YT.res_id2,
YT.res_id3,
YT.res_id4,
(SELECT COUNT(DISTINCT NULLIF(V.res_id,0))
FROM (VALUES(res_id),(res_id2),(res_id3),(res_id4))V(res_id)) met
FROM YourTable YT;