By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
CREATE TABLE groceries (name, quantity) AS
SELECT 'Banana', 15 FROM DUAL UNION ALL
SELECT 'Banana', 5 FROM DUAL UNION ALL
SELECT 'Apple', 10 FROM DUAL UNION ALL
SELECT 'Grape', 17 FROM DUAL UNION ALL
SELECT 'Carrot', 8 FROM DUAL UNION ALL
SELECT 'Potato', 42 FROM DUAL UNION ALL
SELECT 'Tiramisu', 3 FROM DUAL;
7 rows affected
SELECT XMLELEMENT(
"TABLE",
XMLELEMENT(
"THEAD",
XMLELEMENT(
"TR",
XMLELEMENT("TH", XMLATTRIBUTES(2 AS "rowspan"), 'TOTAL'),
XMLELEMENT("TH", XMLATTRIBUTES(3 AS "colspan"), 'Fruits'),
XMLELEMENT("TH", XMLATTRIBUTES(2 AS "colspan"), 'Vegetables'),
XMLELEMENT("TH", XMLATTRIBUTES(2 AS "colspan"), 'Deserts')
),
XMLELEMENT(
"TR",
XMLFOREST(
'Banana' AS "TH",
'Apple' AS "TH",
'Grape' AS "TH",
'Carrot' AS "TH",
'Potato' AS "TH",
'Tiramisu' AS "TH",
'Oreo' AS "TH"
)
)
),
XMLELEMENT(
"TBODY",
XMLELEMENT(
"TR",
XMLFOREST(
'Grand Total' AS "TD",
SUM(CASE name WHEN 'Banana' THEN quantity ELSE 0 END) AS "TD",
SUM(CASE name WHEN 'Apple' THEN quantity ELSE 0 END) AS "TD",
SUM(CASE name WHEN 'Grape' THEN quantity ELSE 0 END) AS "TD",
SUM(CASE name WHEN 'Carrot' THEN quantity ELSE 0 END) AS "TD",
SUM(CASE name WHEN 'Potato' THEN quantity ELSE 0 END) AS "TD",
SUM(CASE name WHEN 'Tiramisu' THEN quantity ELSE 0 END) AS "TD",
| HTML |
|---|
| <TABLE><THEAD><TR><TH rowspan="2">TOTAL</TH><TH colspan="3">Fruits</TH><TH colspan="2">Vegetables</TH><TH colspan="2">Deserts</TH></TR><TR><TH>Banana</TH><TH>Apple</TH><TH>Grape</TH><TH>Carrot</TH><TH>Potato</TH><TH>Tiramisu</TH><TH>Oreo</TH></TR></THEAD><TBODY><TR><TD>Grand Total</TD><TD>20</TD><TD>10</TD><TD>17</TD><TD>8</TD><TD>42</TD><TD>3</TD><TD>0</TD></TR></TBODY></TABLE> |