CODEWITHSUNDEEP
sql-serversql-server-2008
Gary
Gary

Reputation: 27

Map Column names in SQL

I have a Input table 

Header    DisplayName
Field1      EmpName
Field2      EmpID
Field3      IsActive
Filed4      Dept

And a Output table:

Field1  Field2  Field3  Filed4
 A        1       +       Q
 B        2       +       R

How can I map the Display names from Input table onto the output table without explicitly specifying "Select Field1 AS 'EmpName', Field2 AS 'EmpID'" and so on?

Is there a fancy way of mapping the output table in a way that each field picks the Display Name from Input table?

TIA

Upvotes: 1

Views: 1973

Answers (1)

Jason W
Jason W

Reputation: 13179

Not sure about your situation, but I would first try to handle with a view over your output table first. However, if that is now possible, you can use dynamic SQL:

DECLARE @Sql NVARCHAR(MAX) = 'SELECT '
    + STUFF((
        SELECT ', [' + Header + '] AS [' + DisplayName + ']' AS [text()]
        FROM tblInput FOR XML PATH('')), 1, 2, '')
    + ' FROM tblOutput'
EXEC sys.sp_executesql @Sql

It will support variables injected into SQL if you need as well.

DECLARE @Sql NVARCHAR(MAX) = 'SELECT '
    + STUFF((
        SELECT ', [' + Header + '] AS [' + DisplayName + ']' AS [text()]
        FROM tblInput FOR XML PATH('')), 1, 2, '')
    + ' FROM tblOutput O WHERE Field1 = @Field1'
EXEC sys.sp_executesql @Sql, N'@Field1 VARCHAR(50)', @Field1 = 'A'

Upvotes: 4

Related Questions