Solving a problem running in PL / SQL, what would it look like in SQL?

Question

I wrote a solution to the problem using PL / SQL and SQL, and I can't help but think that this can be done 100% in SQL, but I'm afraid to start.

Here is the structure of the two tables (if this helps, the scripts for creating them are at the end of the question)

Table t1 (primary key is displayed in both columns)

ID TYPE 1 A 1 B 1 C 2 A 2 B 3 B 

The Type column is the foreign key of the T2 table, which contains the following data:

Table t2 (primary key - type)

 Type Desc A xx B xx C xx 

Therefore, given the data in T1, I need the result:

For identifier 1, since it has all types in the foreign key table, I would return the literal "All"

For ID 2, since it has two types, I would like to return "A and B" (note the delimiter)

And finally, for ID 3, because it has one type, I would like to return only "B"

As promised, these are scripts for creating all the mentioned objects.

 create table t2(type varchar2(1), description varchar2(100) ) / insert into t2 values ('A', 'xx') / insert into t2 values ('B', 'xx') / insert into t2 values ('C', 'xx') / alter table t2 add constraint t2_pk primary key (type) / create table t1 (id number(10), type varchar2(1) ) / alter table t1 add constraint t1_pk primary key(id, type) / alter table t1 add constraint t1_fk foreign key (type) references t2(type) / insert into t1 values (1, 'A') / insert into t1 values (1, 'B') / insert into t1 values (1, 'C') / insert into t1 values (2, 'A') / insert into t1 values (2, 'B') / insert into t1 values (3, 'B') / 

Answer 1

Something like this should get what you are looking for:

 select id, case when cnt = (select count(distinct type) from t2) then 'All' else ltrim(sys_connect_by_path(type,' & '),' &') end types from ( select t1.id, t2.type, count(*) over (partition by t1.id) cnt, row_number() over (partition by t1.id order by t2.type) rn from t1 inner join t2 on t2.type = t1.type ) where rn = cnt start with rn = 1 connect by prior id = id and prior rn = rn-1; 

I would answer your question +10 if I could post the creation of your object / data script!