Auto rename columns when self joining

Question

Is there a shortcut to auto rename columns (like with a prefix or suffix) when self joining? I am comparing a lot of columns and it is a hassle to list them out and alias them.

A simplified example is:

WITH C as
(
SELECT * FROM A INNER JOIN B ON A.KEY=B.KEY WHERE ....
)
SELECT * from C

It balks obviously because every column has a dupe.

No there isn't unless you start using dynamic sql and the system objects to build your sql. You shouldn't be using * in the column list anyway. You should only select the columns you actually need. — Sean Lange, Nov 17 '15 at 19:11
I'm comparing every column and need them all. I didn't think there was a shortcut. Thanks — user1612851, Nov 17 '15 at 19:12
Then you will have to manually type them in or use dynamic sql to do it. — Sean Lange, Nov 17 '15 at 19:14
You could always get the column list from system tables and concatenate a comma and prefix to save some typing. Such as from http://stackoverflow.com/questions/4849652/find-all-tables-containing-column-with-specified-name for system tables... Work Smarter, not harder! — xQbert, Nov 17 '15 at 19:16

score 0 · Answer 1 · answered Nov 17 '15 at 22:03

Had a few minutes so I threw together this quick example of how you could utilize t-sql to help you build your t-sql. You could use this and then copy and paste into the body of your cte.

create table SelfJoinExample
(
    Col1 int
    , Col2 int
)

select x.y + '.' + name + ' as ' + name + x.y + ', '
from sys.columns
cross join (values('a'),('b'))x(y)
where object_id = object_id('SelfJoinExample')

Auto rename columns when self joining

1 Answers1