Change Column Name Dynamically Using Mapping Table

Question

Interesting but sophisticated for me to deal with this requirement, well I have following information:

CREATE TABLE #mainTable (id INT, ename VARCHAR(20), eaddress VARCHAR(20))
INSERT INTO #mainTable VALUES
(1, 'A', 'B'),
(2, 'B', 'C'),
(3, 'C', 'D')

--OrgTable
id  ename   eaddress
1   A       B
2   B       C
3   C       D

CREATE TABLE #mappingTable (ExportField VARCHAR(20), SelectField VARCHAR(20))
INSERT INTO #mappingTable VALUES
(1, 'ID'),
(2, 'EName'),
(3, 'EAddress')

--#mappingTable
ExportField     SelectField
1               ID
2               EName
3               EAddress

CREATE TABLE #queryBlock(id INT, Equery VARCHAR(MAX))
INSERT INTO #queryBlock VALUES 
(1, 'SELECT id AS [ID], ename AS [EName], eaddress AS [EAddress] FROM #mainTable')

I just have to execute this query which is saved above in the Equery column where I can't make changes directly but the output will be as below with new column name or header from mapping table.

--Desired Output
1   2       3
-------------
1   A       B
2   B       C
3   C       D

I have dozens of similar task of mapping but stuck in thinking of where to start from. Please help me on this, Really I will be thank full to you.

Answer 1

Use the below query.

DECLARE@colList varchar(500)

SELECT @colList=coalesce(@colList+ ',', '') +  convert(varchar(12),SelectField)+' as '+QUOTENAME(cast(ExportField as varchar(10))) 
FROM #mappingTable B

INSERT INTO #queryBlock VALUES 
(1, 'SELECT '+ @colList+'FROM #mainTable')

If you wanted to execute it dynamically,try to script like below.

DECLARE @sql NVARCHAR(MAX)='SELECT '+ @colList+'FROM #mainTable'
EXEC(@sql)

If you wanted to run it from #queryblock tabble ,use the below script.

DECLARE @sql NVARCHAR(MAX)
SELECT @sql=Equery FROM #queryBlock WHERE ID=1
EXEC(@sql)

Answer 2

Whenever you have to set a column's name dynamically you have to use dynamic SQL:

Btw: I'm quite sure, that the structure you showed us will not cover your needs...

CREATE TABLE #mainTable (id INT, ename VARCHAR(20), eaddress VARCHAR(20))
INSERT INTO #mainTable VALUES
(1, 'A', 'B'),
(2, 'B', 'C'),
(3, 'C', 'D')


CREATE TABLE #mappingTable (ExportField VARCHAR(20), SelectField VARCHAR(20))
INSERT INTO #mappingTable VALUES
(1, 'ID'),
(2, 'EName'),
(3, 'EAddress')

DECLARE @tableName VARCHAR(100)='#mainTable'; -- shouldn't this be configured in #mappingTable

DECLARE @statement VARCHAR(MAX)=
(
    SELECT 'SELECT ' + STUFF(
                       (
                        SELECT ',' + QUOTENAME(SelectField) + ' AS ' + QUOTENAME(ExportField)
                        FROM #mappingTable
                        FOR XML PATH('')
                       ),1,1,'' 
                       )
         + ' FROM ' + QUOTENAME(@tableName)
)

PRINT @statement; 

--execute this
EXEC(@statement);
GO

DROP TABLE #mappingTable
DROP TABLE #mainTable

The created statement

SELECT [ID] AS [1],[EName] AS [2],[EAddress] AS [3] FROM [#mainTable]