SQL Server - find horizontal occurrences

user_597243 0
Viewed 36

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

find-occurrences sql-server sql
2 Answers

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;
user_20299830

Generic way to do is something like this:

select 
    a.golf_id
,   a.res_id
,   a.res_id2
,   a.res_id3
,   a.res_id4
,   b.met
from (
    select b.golf_id, count(distinct res_id) as met
    from (
        select golf_id, res_id from tableA where res_id > 0
        union all
        select golf_id, res_id2 from tableA where res_id2 > 0
        union all
        select golf_id, res_id3 from tableA where res_id3 > 0
        union all
        select golf_id, res_id4 from tableA where res_id4 > 0
        ) as b
    group by b.golf_id
    ) as b
join tableA as a
    on a.golf_id = b.golf_id
user_86362780
Related