1 2 3 4 5 6 7 8 9 10 11 12 13 14
|
WITH type_piece AS (
SELECT 4012 type, 'p1' piece FROM dual union ALL
SELECT 4012, 'p2' FROM dual union ALL
SELECT 4013, 'p2' FROM dual union ALL
SELECT 4111, 'p1' FROM dual union ALL
SELECT 4111, 'p2' FROM dual)
SELECT type, RTRIM(REPLACE(REPLACE(XMLAgg(XMLElement("x", piece ) ORDER BY piece), '<x>', ' '), '</x>', ','), ',') pieces
FROM type_piece
GROUP BY type
HAVING RTRIM(REPLACE(REPLACE(XMLAgg(XMLElement("x", piece ) ORDER BY piece), '<x>', ' '), '</x>', ','), ',')
= (SELECT RTRIM(REPLACE(REPLACE(XMLAgg(XMLElement("x", piece ) ORDER BY piece), '<x>', ' '), '</x>', ','), ',') pieces
FROM type_piece
WHERE type=4012) |