I have 4 different tables:
table_price_product (contains information related to the products and their price)
table_price_list (contains the information related to the price list)
prices_per_client (contains the information related to the prices for the different clients given a specific product)
clients (contains the information related to the clients)
This is my SQL FIDDLE: LINK
I got this:
CUSTOMER_NUMBER | CUSTOMER_CLASS_CODE| PRICE
(null) | A | 29223
(null) | B | 33223
112121 | E | 40340
119435 | E | 40340
Now, i want to obtain in a single query all the data related to the products and the clients, replace A,B,C etc with CLASS A, CLASS B, CLASS C respectively but if the customer_class_code is = 'E' i want to obtain the name from table clients AND finally, convert everything and get JSONs for every product. Take a look at this, somehow i would need to add column "PRICES_FOR_CLIENTS" and "groups" because i need those columns to generate the JSON.
SKU |PRICE|PRICES_FOR_CLIENTS|groups|CLASS A|CLASS B|WALMART|SUPERMARKET
99342435|9999 | | |29223 |33223 |40340 |40340
I would like to generate a JSON with that information:
{"sku":"99342435","PRICE":"9999",PRICES_FOR_CLIENTS:[{"group":"CLASS A", "PRICE":"29223"},{"group":"CLASS B", "PRICE":"33223"},{"group":"WALMART","PRICE":"40340"},{"group":"SUPERMARKET", "PRICE":"40340"}]};
Can you help me?
EDIT:
<Item SKU="99342435" Price="9999">
<PRICES_FOR_CLIENTS>
<CLIENT_PRICE>
<Client>WALMART</Client>
<Price>40340</Price>
<Site>USSITE</Site>
</CLIENT_PRICE>
<CLIENT_PRICE>
<Client>SUPERMARKET</Client>
<Price>48343</Price>
<Site>USSITE</Site>
</CLIENT_PRICE>
<CLIENT_PRICE>
<Client>B</Client>
<Price>33223</Price>
<Site>USSITE</Site>
</CLIENT_PRICE>
<CLIENT_PRICE>
<Client>A</Client>
<Price>29223</Price>
<Site>USSITE</Site>
</CLIENT_PRICE>
</PRICES_FOR_CLIENTS>
</Item>
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:
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:
Here is an updated fiddle with that query (Link).
Which produces the following output:
Edit 2: Adding JSON via String Concatination
The following would output JSON via direct string concatination:
And an updated fiddle with this query (Link)
Edit 3: Added Replace **Edit 4: Added analytical function **