Creating a Repeating/Looped Table in Cognos

I need to create a table of lat/lon coordinates with repeating values, in order to calculate distances between all locations.

In the DB I have the following fields to use:

What I'd like to do is have each location repeat in column 1, with column 2 having all the other locations.

etc.

Is there a way to do a Union or Join to get this kind of query setup?

I've tried a few different types of joins and nothing seems to be coming out like I'm looking for it to. Any help on this is greatly appreciated.

It's not a loop. You are not using structured programming to solve the problem. You are using Cognos Analytics, which deals with data. So what you need is a way to appropriately join the dataset to itself.

Use a CROSS JOIN, also known as a cartesian join. This is a join where you don't relate the tables in any way. So rather than something like...

SELECT A.*
, B.*
FROM A
  INNER JOIN B ON B.ID = A.ID

...you want this...

SELECT A.*
, B.*
FROM A
  CROSS JOIN B

To to this in a Cognos Analytics report:

Let's assume you have a query named Query1.

1. Copy Query1. Query2 is created.
2. Create a new query (Query3).
3. Add a join to Query3.
4. Drag Query1 and Query2 onto the boxes leading to the join.
5. Open Query3 and add data items as needed.

Notice you did NOT define the join.

Now, there's a setting in Cognos that may keep you from doing this. If you get a message stating that cross joins are not allowed, you need to do a little extra work.

1. In Query1 and Query2, create a data item named joincol and use the expression 'a'.
2. Open the join leading to Query3 and create a join on joincol--joincol.