Join multiple tables in aggregate with transposed columns

I am trying to create a list using elements from multiple data tables as shown on the left. I would like my data source for list widget to look as shown on the left. First two columns are from Table 1, Msr_Value is from Table 2, and Str_Value is from Table 3.

How can I do that using aggregates? Or will I have to use advanced SQL query? Right now, I create a new Excel using SQL and import, but would prefer not to have multiple data tables, if possible.

I have also attached an example List-item (not using the data shown). Msr_Value is used for the Chart widget; Str_Value is used for Star rating.

Any alternative approaches are welcome.

Hello Rajan,

Well, you can join Table 2 with Table 3 using Hp_No column:

Table2.HP_No = Table3.HP_No

So your problem is just joining this information with Table 1.

What about if we change the datamodel a bit? Something like this:

Then you can do it with an aggregate:

- Join Table 2 with Table 3 by HP_No

- Join result with Table 1 by Msr_ID

- Select what you want to show on your table

- Filter by HP_001

If not, the only way you can achieve this will be using an Advanced SQL Query.

Kind regards,

Rui Barradas