Sunday, January 11, 2015

Representing many-to-many tables in a Report

Consider my case: I've a customer table and a products table. Customers can select multiple products they want to have. The diagram look like this: Customers on the left, Products on the right and a table in between that says which customers like which products. Customers might have multiple products and products might be linked to multiple customers - so a many-to-many relationship.


There're many ways to represent this data in a report. Here's a SQL statement that brings the three tables together:

A default Report created on top of this SQL statement looks like this:


The same SQL in an Interactive Report gives us a bit more possibilities, you could break by Customer and show the products underneath for example:

But another technique I use frequently as well, is by using the listagg (analytical) function.
This function was introduced in Oracle DB 11gR2. Here's a great article that compares listagg with previous possibilities you had in the Oracle Database and also talks about the performance.

Here's my SQL statement:


This shows for every customer, which products they like as a comma separated list:


You could do the reverse as well; for a certain product get a list of customers. I found myself using the listagg function a lot over the years, so hopefully you also find it useful if you didn't know about it yet...

You find the above examples online here.

Next to learning about the LISTAGG Oracle function, I also wanted to give this example to show that although APEX can do a lot of reporting out of the box, the more you know SQL the more options you have to show your data. Long-live SQL!!!

at

3 comments:

  1. Anonymous1/12/2015 9:38 AM

    Nice!
    It would be interesting as well to give some options about how you easily update this kind of data in Apex (add products to a customer and/or add customers to a product). And often the table in the middle has some extra columns (e.g. saying how many products a customer has), which makes it even more complex to create an update screen. I assume you have to use own-written plsql code to do this, but it is a pity that no standard construction exists in apex for this (unless I am wrong). And when the update-page then has a combination of e.g. text fields and check boxes, it is relatively complex.
    Do you have a simple solution for this?
    Geert

    ReplyDelete
  2. Dimitri Gielis1/12/2015 10:50 AM

    Hi Geert,

    That is what the next post is about :)

    Dimitri

    ReplyDelete
  3. Stew1/16/2015 6:42 PM

    Excellent point that, the more SQL you know, the more-sophisticated Apex reports you can create.

    ReplyDelete