T-SQL | Find multiples (with a twist?!)

Question

Code

CREATE TABLE #Temp (ValA varchar(10) NULL, FK_ID int)
INSERT INTO #Temp
SELECT 'A',1
UNION ALL
SELECT 'A',1
UNION ALL
SELECT 'A',1
UNION ALL
SELECT 'A',2
UNION ALL
SELECT 'B',1
UNION ALL
SELECT 'B',2
UNION ALL
SELECT 'C',1
UNION ALL
SELECT 'C',1
UNION ALL
SELECT 'C',1

SELECT 
ValA
, FK_ID
, CASE WHEN COUNT(*) OVER (PARTITION BY ValA, FK_ID) > 1 THEN 1
       ELSE 0
  END IsMultiple 
FROM #Temp

DROP TABLE #Temp

Current Output

ValA    FK_ID   IsMultiple
A   1   1
A   1   1
A   1   1
A   2   0
B   1   0
B   2   0
C   1   1
C   1   1
C   1   1

Desired Output

ValA    FK_ID   IsMultiple
A   1   1
A   1   1
A   1   1
A   2   **1**
B   1   0
B   2   0
C   1   1
C   1   1
C   1   1

Goal I would like to find multiples partitioned by ValA and FK_ID but for those where ValA is repeating and at least 2 of FK_ID is repeating (while at least one other doesn't), I would like those to be marked as 1 (IsMultiple).

i.e. ValA - A has 4 records where 3 records have same FK_ID but one different FK_ID, The whole set should be marked as IsMultiple = 1

Thank you

Answer 1

If you don't have NULL Value in FK_ID

SELECT 
ValA
, FK_ID
, CASE WHEN COUNT(*) OVER (PARTITION BY ValA) > 
      dense_rank() OVER (PARTITION BY ValA ORDER BY FK_ID ASC) + dense_rank() OVER (PARTITION BY ValA ORDER BY FK_ID DESC) -1    -- Get Distinct FK_ID Count
       THEN 1
       ELSE 0
  END IsMultiple 
FROM Temp

Answer 2

Not very elegant but works:

select
    t.*,
    case when tex.ValA is null 
        then 0
        else 1
    end IsMultiple
from #Temp t
left join (
    select
        ValA
    from #Temp
    group by
        ValA, FK_ID
    having
        count(*) > 1
) tex on
    t.ValA = tex.ValA

Here in inner query we select ValAs which have multiple same pairs (ValA, FK_ID) - it's achieved by grouping on (ValA, FG_ID) and taking only with having count(*) > 1.
Then in left join we use this set to mark records with corresponding ValAs as IsMultiple.