Joining a table to itself in SQL and saving the result
In SQL, I am joining the table on myself:
SELECT * FROM table AS a
LEFT JOIN table AS b
ON a.NAME = b.NAME
This way, it fetches all rows that have the same one NAME
that appears in a row in another table (will also return the same row twice!).
Let's say that I want to save the result to a temporary table, say something like:
SELECT * INTO ##temp_table FROM (
SELECT * FROM table AS a
LEFT JOIN table AS b
ON a.NAME = b.NAME
)
Oh my God. SQL says:
The column 'NAME' was specified multiple times.
Reason: SQL can only create a table if each column name is unique.
The obvious solution is to give each column name in the "second table" an alias.
My problem is that the actual tables I am working with have about 40 columns. Giving each of these columns an alias seems like a waste of time. Of course, I don't need every column, so some of them might fall out, but deciding which ones I need now also seems wasteful.
Question: is there a shorthand way to rename each column? For example, can I add each column name with _2
or _a
?
source to share
Okay, you have a query with two linked tables that returns both columns of the table (I don't care if you join the same table with yourself).
So you have two possible outcomes
Show both columns with different alias (AS)
SELECT * INTO ##temp_table FROM (
SELECT a.Name AS NameA, b.Name AS NameB FROM table AS a
LEFT JOIN table AS b
ON a.NAME = b.NAME
)
Or, if you don't want them to be duplicated (because the other will return the same name twice)
SELECT * INTO ##temp_table FROM (
SELECT a.Name FROM table AS a
LEFT JOIN table AS b
ON a.NAME = b.NAME
)
What if you have more colonies? Ok you can just show one of the tables in the JOIN
SELECT * INTO ##temp_table FROM (
SELECT b.* FROM table AS a
LEFT JOIN table AS b
ON a.NAME = b.NAME
)
Sorry for my bad english! Hope this helps you!
source to share
I suggest to query sys.tables
and sys.columns
to quickly rename the fields:
SELECT c.name + '_2,' ColumnName
FROM sys.columns c
JOIN sys.tables t
ON c.object_id = t.object_id
WHERE t.name = 'YourTable'
Or you can combine with the function OBJECT_ID()
:
SELECT c.name + '_2,' ColumnName
FROM sys.columns c
WHERE object_id = OBJECT_ID('YourTable')
source to share