SQL & PL/SQL

Hi ,

I have a issue which I am looking to find the best optimized solution

1. 01 Family ID is dummy data pasted below may have multiple Member id and there are around 111 questions that are answered for each member id
2. Now we need to aggregate value at FAMILY_ID
3. Example if Q1 has Y in any of member ID Family will have Y
4. Example 2 Q4 if have Direct for any of its Member ID its Family ID will have Direct

I thought of using LISTAGG to aggregate value then use case but has multiple issue

1. Will have to write 111 case statement for 111 Questions and each case will have multiple WHEN hence does not sound a great solution to me
2. LISTAGG in oracle will concatenate till 4000 characters only what i response of concatenated string increases 4000

Kindly help

CREATE TABLE AGG_FAM_LEVEL
(
Family_ID NUMBER(50),
Member_ID NUMBER(50),	
Q1 VARCHAR2(4000),
Q2 VARCHAR2(4000),
Q3 VARCHAR2(4000),
Q4 VARCHAR2(4000),
Q5 VARCHAR2(4000),
)
;

INSERT INTO AGG_FAM_LEVEL (Family_ID,Member_ID,Q1,Q2,Q3,Q4,Q5) VALUES (1234,12,'Y','TP','C','No Access','A');
INSERT INTO AGG_FAM_LEVEL (Family_ID,Member_ID,Q1,Q2,Q3,Q4,Q5) VALUES (1234,76,'N','SP','P','will login using VPN','');
INSERT INTO AGG_FAM_LEVEL (Family_ID,Member_ID,Q1,Q2,Q3,Q4,Q5) VALUES (1234,87,'Y','QP','PR','Direct','B');
INSERT INTO AGG_FAM_LEVEL (Family_ID,Member_ID,Q1,Q2,Q3,Q4,Q5) VALUES (1234,65,'N','DF','C','','C');
INSERT INTO AGG_FAM_LEVEL (Family_ID,Member_ID,Q1,Q2,Q3,Q4,Q5) VALUES (45,65,'Y','TP','C','No Access','A');
INSERT INTO AGG_FAM_LEVEL (Family_ID,Member_ID,Q1,Q2,Q3,Q4,Q5) VALUES (45,34,'N','TG','P','will login using VPN','');
INSERT INTO AGG_FAM_LEVEL (Family_ID,Member_ID,Q1,Q2,Q3,Q4,Q5) VALUES (45,32,'Y','QP','PR','Remote','B');
INSERT INTO AGG_FAM_LEVEL (Family_ID,Member_ID,Q1,Q2,Q3,Q4,Q5) VALUES (45,36,'N','DF','C','','C');
INSERT INTO AGG_FAM_LEVEL (Family_ID,Member_ID,Q1,Q2,Q3,Q4,Q5) VALUES (45,36,'N','need to concatenate the strings ','C','CITRIX','C');