sql - Pulling Data with Column Names from a Different Table -

sql - Pulling Data with Column Names from a Different Table -

i'm pulling info table ambiguous column names (table 1). there's table has dictionary of ambiguous column names mean (table 2). there plenty columns in info set don't want type out 'select a_name, b b_name...'. info table 1 rename columns according table 2.

example table 1:

id a1 a2 a3 b1 b2 b3 1 foo1 foo2 foo3 1 1 0 2 bar1 bar2 bar3 2 3 4 ...

example table 2:

column_ref col_definition a1 apples a2 aardvarks a3 androids b1 bears b2 beers b3 boats

example output:

id apples aardvarks androids bears beers boats 1 foo1 foo2 foo3 1 1 0 2 bar1 bar2 bar3 2 3 4 ...

this question comes close: retrieve column names different table?

except have type/copy 200 times every column.

is there way can bring together them names? or similar?

this want except doesn't preserve info type. used nvarchar.

/* create new table info new column headers */ create table table_3 ( /* temp column, delete later */ t3 varchar ) /* select column definitions temp table */ select * #temptable ( select a.col_definition table_2 bring together ( /* column names table_1 bring together on */ select column_name sandbox.information_schema.columns table_name = n'table_1' ) b on a.column_ref=b.column_name ) t declare @coldf nvarchar(max) declare @sql nvarchar(max) /* loop through column definitions in #temptable */ while exists (select * #temptable) begin select @coldf = (select top 1 coldf #temptable order coldf asc) /* add together column using each column definition column name */ set @sql = 'alter table table_3 add together ' + @coldf + ' nvarchar(max)' exec (@sql) delete #temptable coldf = @coldf end /* remove temp table */ drop table #temptable /* remove temp column */ alter table table_3 drop column t3 /* re-create info table_1 table_3 */ insert table_3 select * table_1 /* view results */ select * table_3

sql sql-server join