Follow

How to create all possible combinations across two (not joined) tables

This is one of the more odd requests I have had for a while, so I thought I would document the solution.

The question

We have a customer who wants under certain conditions create all combinations from the certain G/L Account No. concatenated with some dimension value... so he can create a reporting dimension...

so from the account no.

1410

And list of dimensions

10

20

30

40

50

60

He would like to have rows created in the staging area of

1410-10

1410-20

1410-30

..

1410-60

Can we do this in TX ? if so, how ?

Note: As added information, there is no join between the tables.

The solution

The easiest way is to create a custom view using the following syntax.

 

CREATE VIEW [dbo].[vAccountDims] AS

SELECT [AccountNo] + '-' + DimID AS AccountDim

FROM [dbo].[Accounts_V] A, [dbo].[Dims_V]

 

With data as per above, the results of the view looks like this:

 

Account_Dim.png

I have attached a project in timeXtender 4.5.37 format as a reference. Please be aware that this might not load in all future versions of timeXtender.

 

Was this article helpful?
2 out of 2 found this helpful
Have more questions? Submit a request

3 Comments

  • 0
    Avatar
    Kamil Karbowiak

    Thanks Thomas... Works like a charm...

  • 0
    Avatar
    Dirk Van der Straeten

    Hi Thomas,

    Strange that you would even use a join function at all, this is a standard crossjoin which you could also write:

     

    CREATE VIEW [dbo].[vAccountDims] AS

    SELECT  [AccountNo] + '-' + DimID AS AccountDim

    FROM  [dbo].[Accounts_V], [dbo].[Dims_V]

     

    Just put the two tables in the from clause, will perform a full crossjoin.

    Regards

    Dirk

  • 0
    Avatar
    Thomas Christiansen

    Hi Dirk

    I didn't even think of that. Thank you for pointing that out, I stand corrected :-)

    I updated the post to reflect your suggestion, as it is more intuitive and there is no need for the dummy join.

    Best regards

    Thomas

Please sign in to leave a comment.