We have a new community! Please visit support.timextender.com

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

Note: This is an old article.  However, these tips may still apply to newer versions.  

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.


And list of dimensions







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






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:



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


  • 0
    Kamil Karbowiak

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

  • 0
    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.



  • 0
    Permanently deleted user

    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


  • 0
    Stuart Cuthbertson

    I'm years late to this party, but please don't use or promote the ANSI SQL-89 syntax for any kind of join, cross (cartesian) or otherwise. 

    SQL-92 presented the replacement syntax of 'CROSS JOIN ...' and 1989-style has been discouraged ever since. Whilst it does still work on Microsoft SQL Server, it won't for ever. Microsoft's transact-SQL documentation is pretty clear that they want everyone to use SQL-92 syntax (https://docs.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-2017).

    The SQL Community has been mostly in agreement for literally ages that this is a bad way to write code, for example:


    https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins (I recognize some disagreement in the comments there, but the overall consensus swings to avoiding SQL-89 syntax.)

    The correct way to write the query required for this view is: 


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

    FROM  [dbo].[Accounts_V]

    CROSS JOIN [dbo].[Dims_V]

    Edited by Stuart Cuthbertson
Please sign in to leave a comment.