Jack Jack - 4 months ago 7
SQL Question

Select table and column dynamically based on other table rows

I have following table and values,

Tb_name column_name1 column_name2
Citator_KTLO_CC Date_Created Date_Modified
Citator_KTLO_QA Date_Created Date_Modified


I want to select dynamically column from table, so the result is like this:

Select Date_Created,Date_Modified from Citator_KTLO_CC


and in next loop it will select for second row, like

Select Date_Created,Date_Modified from Citator_KTLO_QA


How can i do this by using dynamic sql ?

any example are appreciated.

Answer

here is an example of how to do this.
Since you dont post many info I just assume that the table containing all the tablenames is called 'tables'

Also this will only work if all tables have the same column types.

-- create a test table you dont need this
create table tables (tb_name varchar(100) primary key, field1 varchar(100), field2 varchar(100))

-- fill my test table you dont need this
insert into tables values ('table1', 'field1', 'field2')
insert into tables values ('table2', 'foo1', 'foo2')
insert into tables values ('table3', 'test1', 'test2')

-- this is the actual code you need, replace the names with your real names
declare @sql varchar(max) = ''
declare @tb_name varchar(100) = ''
declare @field1 varchar(100) = ''
declare @field2 varchar(100) = ''

declare myCursor cursor for
  select tb_name, field1, field2 from tables -- dont know how your table is called

open myCursor
fetch next from myCursor into @tb_name, @field1, @field2
while @@FETCH_STATUS = 0
begin
  set @sql = @sql + ' select ' + @field1 + ', ' + @field2 + ' from ' +  @tb_name + ' union all '
  fetch next from myCursor into @tb_name, @field1, @field2
end
close myCursor
deallocate myCursor 

select @sql = left(@sql, len(@sql) - 10) 

exec (@sql)
Comments