By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t
("REQNO" int, "ANA" varchar2(1), "ANSWER" int)
;
INSERT ALL
INTO t ("REQNO", "ANA", "ANSWER")
VALUES (1, 'A', 7)
INTO t ("REQNO", "ANA", "ANSWER")
VALUES (1, 'B', 14)
INTO t ("REQNO", "ANA", "ANSWER")
VALUES (1, 'C', 18)
INTO t ("REQNO", "ANA", "ANSWER")
VALUES (1, 'X', 250)
INTO t ("REQNO", "ANA", "ANSWER")
VALUES (2, 'A', 8)
INTO t ("REQNO", "ANA", "ANSWER")
VALUES (2, 'X', 35)
INTO t ("REQNO", "ANA", "ANSWER")
VALUES (2, 'Y', 125)
INTO t ("REQNO", "ANA", "ANSWER")
VALUES (3, 'A', 8)
INTO t ("REQNO", "ANA", "ANSWER")
VALUES (3, 'B', 16)
INTO t ("REQNO", "ANA", "ANSWER")
VALUES (3, 'C', 20)
INTO t ("REQNO", "ANA", "ANSWER")
VALUES (3, 'Z', 100)
INTO t ("REQNO", "ANA", "ANSWER")
VALUES (4, 'X', 115)
INTO t ("REQNO", "ANA", "ANSWER")
VALUES (4, 'Y', 355)
INTO t ("REQNO", "ANA", "ANSWER")
VALUES (5, 'A', 6)
INTO t ("REQNO", "ANA", "ANSWER")
VALUES (5, 'B', 15)
INTO t ("REQNO", "ANA", "ANSWER")
VALUES (5, 'C', 22)
INTO t ("REQNO", "ANA", "ANSWER")
VALUES (5, 'X', 300)
INTO t ("REQNO", "ANA", "ANSWER")
19 rows affected
with req
AS
(select reqno from t where ana IN ('A','B','C')
GROUP BY reqno HAVING
count(DISTINCT ana) = 3
)
select * FROM
(
select * from t where
exists ( select 1 from req r where t.reqno = r.reqno )
)
PIVOT(
min(answer) for ana in ('A' as A ,'B' as B,'C' as C,
'X' as X,'Y' as Y,'Z' as Z)
) ORDER BY reqno;
| REQNO | A | B | C | X | Y | Z |
|---|---|---|---|---|---|---|
| 1 | 7 | 14 | 18 | 250 | null | null |
| 3 | 8 | 16 | 20 | null | null | 100 |
| 5 | 6 | 15 | 22 | 300 | 108 | null |