我认为您可以使用以下查询替换大多数代码。您可能需要调整IN子句，如果要更改很多客户列表，这会很麻烦。但这会复制您的结果：
SELECt *FROM (SELECt DECODE(ppc.customer_class_pre, 'E', c.description, ppc.customer_class_pre) AS IDENTIFIER, tpp.item_pre, tpp.price AS ITEM_PRICE, ppc.price FROM table_price_list tpl INNER JOIN table_price_product tpp ON tpp.list_header_id = tpl.list_header_id AND tpp.request_id = tpl.request_id INNER JOIN prices_per_client ppc ON tpp.item_pre = ppc.item_pre LEFT JOIN clients c ON ppc.customer_number = c.account_number WHERe SYSDATE BETWEEN NVL(tpp.start_date_active, SYSDATE) AND NVL(tpp.end_date_active, SYSDATE+1))PIVOT (AVG(PRICE) FOR IDENTIFIER IN ('A' AS CLASS_A , 'B' AS CLASS_B, 'SUPERMARKET' AS SUPERMARKET, 'WALMART' AS WALMART));这是一个更新小提琴。
至于JSON输出，如果您使用的是更高版本，它将变得更加容易，因为它现在已成为核心功能的一部分。
编辑：每个注释添加XML功能
您可以查询以下查询：
SELECt XMLSERIALIZE(ConTENT XMLELEMENT("Item", XMLATTRIBUTES(sub.item_pre AS "SKU", sub.item_price AS "Price"), XMLELEMENT("PRICES_FOR_CLIENTS", XMLAGG(XMLELEMENT("CLIENT_PRICE", XMLFOREST(sub.identifier AS "Client", sub.price AS "Price"))))) AS CLOB INDENT) FROM (SELECt DECODE(ppc.customer_class_pre, 'E', c.description, ppc.customer_class_pre) AS IDENTIFIER, tpp.item_pre, tpp.price AS ITEM_PRICE, avg(ppc.price) AS PRICE FROM table_price_list tpl INNER JOIN table_price_product tpp ON tpp.list_header_id = tpl.list_header_id AND tpp.request_id = tpl.request_id INNER JOIN prices_per_client ppc ON tpp.item_pre = ppc.item_pre LEFT JOIN clients c ON ppc.customer_number = c.account_number WHERe SYSDATE BETWEEN NVL(tpp.start_date_active, SYSDATE) AND NVL(tpp.end_date_active, SYSDATE+1) GROUP BY DECODE(ppc.customer_class_pre, 'E', c.description, ppc.customer_class_pre), tpp.item_pre, tpp.price) subWHERe sub.identifier IS NOT NULLGROUP BY sub.item_pre, sub.item_price;这是该查询的更新小提琴（link）。
产生以下输出：
<Item SKU="99342435" Price="9999"> <PRICES_FOR_CLIENTS> <CLIENT_PRICE> <Client>WALMART</Client> <Price>40340</Price> </CLIENT_PRICE> <CLIENT_PRICE> <Client>SUPERMARKET</Client> <Price>48343</Price> </CLIENT_PRICE> <CLIENT_PRICE> <Client>B</Client> <Price>33223</Price> </CLIENT_PRICE> <CLIENT_PRICE> <Client>A</Client> <Price>29223</Price> </CLIENT_PRICE> </PRICES_FOR_CLIENTS></Item>
编辑2：通过字符串合并添加JSON
以下将通过直接字符串概括输出JSON：
SELECt '{"sku":"'||sub.item_pre||'","PRICE":"'||sub.item_price||'",PRICES_FOR_CLIENTS:['||listagg('{"group":"'||sub.identifier||'","PRICE":"'||sub.price||'"}',',') WITHIN GROUP (ORDER BY sub.identifier)||']};' AS JSON FROM (SELECt DECODE(ppc.customer_class_pre, 'E', c.description, ppc.customer_class_pre) AS IDENTIFIER, tpp.item_pre, replace(tpp.price, ',', '.') AS ITEM_PRICE, REPLACE(avg(ppc.price), ',', '.') AS PRICE, tpl.request_id, max(tpl.request_id) over (partition by tpp.item_pre) as max_request FROM table_price_list tpl INNER JOIN table_price_product tpp ON tpp.list_header_id = tpl.list_header_id AND tpp.request_id = tpl.request_id INNER JOIN prices_per_client ppc ON tpp.item_pre = ppc.item_pre LEFT JOIN clients c ON ppc.customer_number = c.account_number WHERe SYSDATE BETWEEN NVL(tpp.start_date_active, SYSDATE) AND NVL(tpp.end_date_active, SYSDATE+1) GROUP BY DECODE(ppc.customer_class_pre, 'E', c.description, ppc.customer_class_pre), tpp.item_pre, tpp.price, tpl.request_id) sub WHERe sub.identifier IS NOT NULLand sub.request_id = sub.max_requestGROUP BY sub.item_pre, sub.item_price;以及与此查询相关的更新小提琴（link）
编辑3：添加了替换 编辑4：添加了分析功能
