-1

I have a statement with a lot of joins. I want to get all columns for the report requester but I also wanted to include the table name in the name of the column. Instead of getting a column named 'name' I want to get a column named 'user.name' based on its table. I want this process to be automatically done without writing table names.

SELECT * AS TABLE_NAME.* FROM THOSE_TABLES
Dale K
  • 25,246
  • 15
  • 42
  • 71
syrkull
  • 2,295
  • 4
  • 35
  • 68

1 Answers1

2

You need to use dynamic sql. First create a key value relation of your used table name and its corresponding column name using information_schema and stuff.

select column_name + '.' + table_name 
from information_schema.columns 
where table_name in ( 'table1', 'table2' ...)

After that use this value in your final output query for declaring column names, but this is also need to be done using dynamic sql and finally need to execute using sp_execute or sp_executesql to get your final result.

Final query will be like this...

declare @col varchar(max)
set @col = Select stuff( 
          (select ', ' + column_name + '.' + table_name 
           from information_schema.columns 
           where table_name in ( 'table1', 'table2' ...) for xml 
           path('')),1,1,'')

declare @query nvarchar(max) = '
select ' + @col + ' 
from table1 
inner join table2 on table1.id = table2.id '

exec sp_executesql @query

You can change some of the portion in the query as per your use and condition.

El.Hum
  • 1,479
  • 3
  • 14
  • 23
DarkRob
  • 3,843
  • 1
  • 10
  • 27
  • getting: Lookup Error - SQL Server Database Error: Incorrect syntax near the keyword 'Select'. this is line 2 select. – syrkull Sep 01 '19 at 16:25
  • @syrkull: Can you share some sample table structure on which you're trying to apply inner join and need result.....or you can make fiddle to produce error, which is more helpful. – DarkRob Sep 02 '19 at 04:01