compare within groups to fetch common value

Question

I have a table with the follwing data

Case 1:

table1
-------------
id      type
-------------
1         X
1         Y
2         X
3         Z
3         X
-------------

Now as you see X is common to all the id ,so i need to return X in this case

Case2 :

table1
-------------
id      type
-------------
1         X
1         Y
2         X
2         Y
3         X
3         Y
--------------

In this case both X and Y are common,then i need to return both Xand Y comma seperated (X,y)

Case 3

table1
-------------
id      type
-------------
1         X
1         Y
2         X
2         Y
3         X
3         Y
4         NULL
------------------

If a null came to any of the record , i need to return NULL

Actually ,the data i have shouwn you , is been populated from 3 tables , so i have already written the query for that ,but now i need to compare the groups for the common data within groups ,that is confusing me ,how to compare the groups ?

Note :Here group is based on ID

Any help would be appriciated

Answer 1

you could count the occurances compared to the count of the IDs?

with data as (select rownum id, 'X' type from dual connect by level <= 3
              union all
              select rownum id, 'Y' type from dual connect by level <= 3
              union all
              select 3 id, 'Z' type from dual)
select wm_concat(distinct type)
  from (select type, count(*) over (partition by type) cnt, count(distinct id) over () total_ids
          from data)
 where cnt = total_ids;

in 11g you have LISTAGG instead of WM_CONCAT of course. if for each id, the same type occurs many times, you can change count(*) over (partition by type) to count(distinct id) over (partition by type)

edit:

If you had

3, Z
3, NULL 

(rather than 4, NULL) and also want to return a NULL rather than a delimited list in that case then you could add a check (with the 4, NULL above it would return a null even on the prior SQL version as the counts would'nt tie up):

       with data as (select rownum id, 'X' type from dual connect by level <= 3
                      union all
                      select rownum id, 'Y' type from dual connect by level <= 3
                      union all
                      select 3 id, 'Z' type from dual)
        select wm_concat(distinct type)
          from (select type, count(*) over (partition by type) cnt, count(distinct id) over 

() total_ids,
                       max(case when type is null then 'Y' else 'N' end) over () has_null_in_set
                  from data)
         where cnt = total_ids
          and has_null_in_set = 'N';