TO find out same student is associated with how many subjects in different group

Question

I have below Table, Need to exract those records where Same Value is associated with more than one different GroupId. If the same value is being duplicated under same GroupId then don't need to fetch that.

GroupId Value
11       AAA
11       AAA
11       BBB
22       AAA
22       CCC
22       CCC
33       AAA
33       DDD
33       BBB

I have tried it with Count(*) with Having Count(*)>1 but nothing giving me the desired Output

The Output I need as below, where it tells the same Value is associated with different GroupId. Here if the same value is being duplicated in same GroupId I am not concerned about that. I just need those values which are available in more than one GroupId.

Desired OutPut-

Column  Value   GroupId
1        AAA    11
1        AAA    22
1        AAA    33
2        BBB    22
2        BBB    33

Answer 1

You may try this.

; WITH CTE AS (  
    SELECT DISTINCT GROUPID, VALUE FROM @TAB 
)  
, CT AS (  
    SELECT DENSE_RANK() OVER (PARTITION BY VALUE ORDER BY GROUPID, VALUE) AS SLNO, * FROM CTE
)  
SELECT DENSE_RANK() OVER ( ORDER BY VALUE) AS [COLUMN], * FROM CTE 
    WHERE VALUE IN (SELECT  VALUE FROM CT WHERE SLNO>1)

Answer 2

With EXISTS:

select distinct t.*
from tablename t
where exists (
  select 1 from tablename
  where value = t.value and groupid <> t.groupid
)
order by t.value, t.groupid

See the demo.
Results:

> GroupId | Value
> ------: | :----
>      11 | AAA  
>      22 | AAA  
>      33 | AAA  
>      11 | BBB  
>      33 | BBB