asked on

How to insert column between two CTEs

I have two CTE's which each have two rows and select them with one select statement:

SELECT * FROM cte1, cte2

When I do the select statement, I would like to have an empty column between each CTE. Is this possible? I am using Snwoflake.

ASKER CERTIFIED SOLUTION

Qlemo

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

slightwv (䄆 Netminder)

When joining two tables like that you normally include the columns to join them. Otherwise you end up with a Cartesian Join and those are normally very bad.

If you want the results from both, maybe a union all?

It is also a BAD idea to ever use 'select *'. You should ALWAYS use the column names.

Assuming they have the same number of columns and data types:

SELECT col1,col2,col3 FROM cte1
union all
SELECT col1,col2,col3 FROM cte2

Open in new window

PortletPaul

This is a guessing game - because you have not supplied any sample results AND we really need to see your "expected result"
let's say cte1 produces this:

C1col1 C1col2
a b
c d

and cte2 produces this:

C2colA C2colB

123 456

987 654

Is the expected result like this?

C1col1 C1col2 BlankColumn C2colA C2colB
a b 123 456
c d 987 654

You may have noticed that some "magic" applies in the sample above because there is no obvious logic that aligns "a" or "b" to "123" or "456" etc.

Is you problem similar to this? or is it obvious how the rows of CTE1 relate to CTE2?

Do CTE1 and CTE2 ALWAYS have the same number of rows?

What happens if the number of rows differ?

PLEASE provide a sample of data from CTE1 and a sample of data from CTE2
AND also provide an "expected result"

NOTE too, that the SQL for CTE1 and CTE2 could reveal a solution

i.e. We can provide an informed suggestion once we have more information.