SQL Column Names from a Excel Sheet -

SQL Column Names from a Excel Sheet -

im using ssms 2014 , sql server 2014. need alter column names @ end of query result using excel file or table data.

after select statements , stuff table info example

+---------+---------+------+ | col1 | col2 | col3 | +---------+---------+------+ | value 1 | value 2 | 123 | | value 2 | value 2 | 456 | | value 3 | value 3 | 789 | +---------+---------+------+

and table or excelfile

+----+---------+-----------+-----------+ | id | colname | language | add-on | +----+---------+-----------+-----------+ | 1 | col1 | d | 123 | | 2 | col2 | d | 456 | | 3 | col3 | d | 789 | | 4 | col1 | e | 123 | | 5 | col2 | e | 456 | | 6 | col3 | e | 789 | +----+---------+-----------+-----------+

what seek add-on value of each column , add together column name. should add together values specific language. @setlang = 'd'

col1 + add-on col2 + add-on col3 + add-on +-------------+-------------+---------+ | col1 123 | col2 456 | col3789 | +-------------+-------------+---------+ | value 1 | value 2 | 123 | | value 2 | value 2 | 456 | | value 3 | value 3 | 789 | +-------------+-------------+---------+

i tried on information_schema.columns , filter table = 'resulttable' , column_name = @cname. maybe need loop each columnname.

thanks reading , trying help me.

give go - uses table, not excel file (but seems alternative in question). have made temporary tables , filled them values, not need this. need replace out references tempdb name of database tables kept , temp tables #original , #excelinfo table names.

i have used temp table add together 'id identity(1,1)` column table holding original data. needed maintain unpivot in check; if can modify table include id, create things easier, if not, can insert temp table have done.

the script shorter looks - whole first bit setting example; real reply starts @ line declares language variable.

/* script between first 2 dividing lines of dashes used set example. bit want "-- test variables --" line. */ ----------------------------------------------------------------------------------------------------------------------- if object_id('tempdb..#original') not null drop table #original if object_id('tempdb..#excelinfo') not null drop table #excelinfo create table #original ( col1 varchar(50) ,col2 varchar(50) ,col3 varchar(50)) create table #excelinfo ( id int identity(1,1) not null ,colname varchar(50) not null ,[language] char(1) not null ,addition int not null) insert #original select * ( select 'value 1' col1,'value 2' col2 ,123 col3 union select 'value 2' ,'value 2' ,456 union select 'value 3' ,'value 3' ,789) order col1 insert #excelinfo (colname,[language],addition) select * ( select 'col1' colname, 'd' [language], 123 add-on union select 'col2','d',456 union select 'col3','d',789 union select 'col1','e',123 union select 'col2','e',456 union select 'col3','e',789) order [language], add-on ----------------------------------------------------------------------------------------------------------------------- -- test variables -- declare @setlang char(1) = 'd' ----------------------------------------------------------------------------------------------------------------------- -- create default empty, not null on our dynamic string, can added declare @columns varchar(max) = '' declare @sql varchar(max) create table #originalcolumns ( id int identity(1,1) ,name varchar(50)) create table #basicresult (id int not null, name varchar(50), value varchar(50)) -- if can add together id column original table, bit unecessary - can utilize yours in place of table create table #original_with_id ( id int identity(1,1) ,col1 varchar(50) ,col2 varchar(50) ,col3 varchar(50)) insert #original_with_id select * #original ----------------------------------------------------------------------------------------------------------------------- -- list out columns , set list in variable. insert #originalcolumns select quotename(col.name) tempdb.sys.columns col col.object_id = object_id('tempdb.dbo.#original_with_id') -- we're not interested in identity column @ moment , col.name <> 'id' -- maintain in same order listed on table order col.column_id select @columns = @columns + ',' + name #originalcolumns -- clip off leading comma select @columns = substring(@columns,2,len(@columns)) -- total list of everything, creating our new list of columns go, using id column maintain mark on -- row each record came set @sql = 'insert #basicresult select id, new.name, value (select id, name, value #original_with_id unpivot (value name in (' + @columns + ')) unpvt) old bring together (select colname, convert(varchar(50),colname) + '' '' + convert(varchar(50),addition) name #excelinfo [language] = ''' + @setlang + ''') new on old.name = new.colname' print @sql exec (@sql) -- update our list of columns new column headings set @columns = '' select @columns = @columns + ',' + quotename(name) (select distinct name #basicresult) names select @columns = substring(@columns,2,len(@columns)) -- pivout our results out original format, new column headings (include id if want) set @sql = 'select /*id,*/ ' + @columns + ' (select id, name,value #basicresult) pivot (max(value) name in (' + @columns + ')) pvt' print @sql exec (@sql) -- clean -- drop table #originalcolumns drop table #basicresult

hope helps! there may more efficient way this... i'm not sure.

sql excel ssms sql-server-2014-express