CODEWITHSUNDEEP
sqljoin
dplanet
dplanet

Reputation: 5413

Joining a table onto itself in SQL and saving the result

In SQL, I am joining a table onto itself:

SELECT * FROM table AS a
LEFT JOIN table AS b
ON a.NAME = b.NAME

So it's fetching all the rows which have the same NAME appearing in a row of the other table (will also return the same row twice!).

Let's say that I want to save the result into 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 dear. SQL says:

The column 'NAME' was specified multiple times.

Reason: SQL can only create a table if every column name is unique.

Obvious solution: give every column name in the "second table" an alias.

My problem is that the actual tables I'm working with have about 40 columns. Giving every one of those columns an alias seems like a wasteful use of time. Sure, I don't need every column so could drop some of them, but deciding which ones I require just now also seems wasteful.

Question: is there a shorthand way to rename every column? For example, can I append every column name with a _2 or an _a?

Upvotes: 0

Views: 3321

Answers (2)

GianlucaBobbio
GianlucaBobbio

Reputation: 184

Ok, you have a query, with 2 joined tables, wich returns both tables columns (i don't care if you are joining the same table with itself).

So you have two possible results

Show both colums, with differents 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 duplicated (because the other will return two times the same name)

SELECT * INTO ##temp_table FROM (
    SELECT a.Name FROM table AS a
    LEFT JOIN table AS b
    ON a.NAME = b.NAME
)

And what if you have more colums? 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! I hope this can help you!

Upvotes: 1

Hart CO
Hart CO

Reputation: 34784

I suggest querying sys.tables and sys.columns to get your renamed fields quickly:

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 OBJECT_ID() function:

SELECT c.name + '_2,' ColumnName
FROM sys.columns c 
WHERE object_id = OBJECT_ID('YourTable')

Upvotes: 0

Related Questions