Creating JSON and editing a complex query (oracle 11g)

I think you can replace most of your code with the following query. You may need to adjust the IN clause, which is a pain if you are changing the customer list a lot. But this replicates your results:

SELECT *FROM (SELECT DECODE(ppc.customer_class_code, 'E', c.description, ppc.customer_class_code) AS IDENTIFIER, tpp.item_code, 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_code = ppc.item_code      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));

Here is an update fiddle.

As for the JSON output, it would be much easier if you were on a later version as it is now part of the core functionality.

EDIT: Adding XML Functionality per Comments

You could check out this query:

SELECT XMLSERIALIZE(CONTENT                    XMLELEMENT("Item",                               XMLATTRIBUTES(sub.item_code 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_code, 'E', c.description, ppc.customer_class_code) AS IDENTIFIER, tpp.item_code, 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_code = ppc.item_code      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_code, 'E', c.description, ppc.customer_class_code), tpp.item_code, tpp.price) subWHERE sub.identifier IS NOT NULLGROUP BY sub.item_code, sub.item_price;

Here is an updated fiddle with that query (Link).

Which produces the following output:

<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>

Edit 2: Adding JSON via String Concatination

The following would output JSON via direct string concatination:

SELECT '{"sku":"'||sub.item_code||'","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_code, 'E', c.description, ppc.customer_class_code) AS IDENTIFIER, tpp.item_code, replace(tpp.price, ',', '.') AS ITEM_PRICE, REPLACE(avg(ppc.price), ',', '.') AS PRICE,       tpl.request_id, max(tpl.request_id) over (partition by tpp.item_code) 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_code = ppc.item_code      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_code, 'E', c.description, ppc.customer_class_code), tpp.item_code, tpp.price, tpl.request_id) sub WHERE sub.identifier IS NOT NULLand sub.request_id = sub.max_requestGROUP BY sub.item_code, sub.item_price;

And an updated fiddle with this query (Link)

Edit 3: Added Replace**Edit 4: Added analytical function **