clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2043036 fiddles created (16640 in the last week).

CREATE TABLE t AS SELECT 'a' AS grp, 3 AS l FROM DUAL UNION ALL SELECT 'a' AS grp, 5 AS l FROM DUAL UNION ALL SELECT 'a' AS grp, NULL AS l FROM DUAL UNION ALL SELECT 'a' AS grp, 7 AS l FROM DUAL UNION ALL SELECT 'b' AS grp, 2 AS l FROM DUAL UNION ALL SELECT 'b' AS grp, 6 AS l FROM DUAL UNION ALL SELECT 'c' AS grp, NULL AS l FROM dual
7 rows affected
 hidden batch(es)


SELECT * FROM t
GRP L
a 3
a 5
a
a 7
b 2
b 6
c
 hidden batch(es)


WITH cte AS ( SELECT grp, LISTAGG(l, '*') AS product_expression FROM t GROUP BY grp ) SELECT c.*, s.val AS product_value FROM cte c OUTER APPLY(SELECT * FROM XMLTABLE('/ROWSET/ROW/*' passing dbms_xmlgen.getXMLType('SELECT ' || c.product_expression || ' FROM dual') COLUMNS val NUMBER PATH '.') WHERE c.product_expression IS NOT NULL) s
GRP PRODUCT_EXPRESSION PRODUCT_VALUE
b 2*6 12
c
a 3*5*7 105
 hidden batch(es)