SQL Column Names from a Excel Sheet

Question

Im using SSMS 2014 and SQL Server 2014. I need to change the column names at the end of a query result by using a excel file or table with the data.

After some SELECT statements and stuff i get a table with data for example

+---------+---------+------+
|  Col1   |  Col2   | Col3 |
+---------+---------+------+
| Value 1 | Value 2 |  123 |
| Value 2 | Value 2 |  456 |
| Value 3 | Value 3 |  789 |
+---------+---------+------+

And the table or excelfile

+----+---------+-----------+-----------+
| ID | ColName |  Language |  Addition |
+----+---------+-----------+-----------+
|  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 i try to do is to get the addition value of each column and add it to the column name. It should only add the values with the specific language. @setLang = 'D'

Col1 + Addition
Col2 + Addition
Col3 + Addition

+-------------+-------------+---------+
|  Col1 123   |  Col2 456   | Col3789 |
+-------------+-------------+---------+
| Value 1     | Value 2     |  123    |
| Value 2     | Value 2     |  456    |
| Value 3     | Value 3     |  789    |
+-------------+-------------+---------+

I tried it over Information_Schema.Columns and filter with where table = 'resultTable' and Column_name = @cName. Maybe i need a loop to get each columnname.

Thanks for reading and trying to help me.

Answer 1

Okay i tried it again but now without the Excelfile. I made a CSV out of the Excelfile and insert it with Bulk to my created Table:

IF NOT EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CSVTest')
BEGIN
CREATE TABLE _DICTIONARY
( _TableName VARCHAR (20),
_ColumnName  VARCHAR  (20),
_Language    VARCHAR  (20),
_FieldShort  VARCHAR (50),
_FieldMid    VARCHAR (50),
_FieldLong   VARCHAR (50)
)
BULK
INSERT _DICTIONARY
FROM 'C:\_DICTIONARY.csv'
WITH
(
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)
END

After that i rename all Columns by using a Cursor

DECLARE @dic_tableName            as nvarchar(50),
        @dic_columnName           as nvarchar(50),
        @db_tableName              as nvarchar(50),
        @db_columnName            as nvarchar(50);


DECLARE C CURSOR FAST_FORWARD FOR
    SELECT _TableName, _ColumnName FROM _DICTIONARY
OPEN C;

FETCH NEXT FROM C INTO @dic_tableName, @dic_columnName;

WHILE @@FETCH_STATUS = 0
BEGIN
  IF EXISTS(SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @dic_tableName AND COLUMN_NAME = @dic_columnName)
BEGIN
  SET @db_tableName = @dic_tableName + '.' + @dic_columnName
  SET @db_columnName = @dic_tableName + '_' + @dic_columnName
  EXEC sp_rename @db_tableName, @db_columnName ,'COLUMN'
  FETCH NEXT FROM C INTO @dic_tableName, @dic_columnName;
END
  ELSE
BEGIN
  FETCH NEXT FROM C INTO @dic_tableName, @dic_columnName;
END
END

CLOSE C;
DEALLOCATE C;

Its doing its job.