Ndtyjky

I need help on a getting my column names as values in another table in SQL 2008.

For Example

Table A

Type     Col1      Col2      Col3     Col4      Col5



101      1         2         3        2         5

102      4         2         3        2         0

103      2         1         0        0         5

103      7         2         0        0         5

105      8         3         0        0         0

Table B

ColID        ColName



Col1         Math

Col2         English

Col3         French

Col4         Fine Arts

Col5         Biology

So basically the Table B holds the column name to be used when selecting from Table A
Such that, if i want to do a sample code like

Select Col1,Col2,Col3 from TableA

It should look like

Select Col1 as Math, Col2 as English, Col3 as French from Table A........

Did all i could but could not get any code giving me what i want....

Need help.

Note*
Reason i designed the table like this is because the column names can change very frequently by user and instead of having to alter code everytime, this should work no matter the changes made to column names when i export to excel.

To get this done with your current design you will have to use dynamic SQL. I wont go into the pros and cons of dynamic SQL here. For more info you can go here. I highly suggest reading up on it if you are not familiar with dynamic SQL.

CREATE TABLE #TableA (Type int, Col1 int, Col2 int, Col3 int, Col4 int, Col5 int);



CREATE TABLE #TableB (ColId varchar(50), ColName varchar(50));



INSERT INTO #TableA VALUES

(101, 1, 2, 3, 2, 5),

(102, 4, 2, 3, 2, 0),

(103, 2, 1, 0, 0, 5),

(103, 7, 2, 0, 0, 5),

(105, 8, 3, 0, 0, 0);



INSERT INTO #TableB VALUES

('Col1', 'Math'),

('Col2', 'English'),

('Col3', 'French'),

('Col4', 'Fine Arts'),

('Col5', 'Biology');



DECLARE @Sql nvarchar(MAX);



/*   Build SELECT   */

SET @Sql = 'SELECT ';



SELECT @Sql = @Sql + ColId + ' AS ''' + ColName + ''', ' FROM #TableB;



/*   Remove trailing comma   */

SET @Sql = (SELECT LEFT(@Sql, LEN(@Sql)-1));



/*   Add FROM   */

SET @Sql = @Sql + ' FROM #TableA';



/*   Output query   */

SELECT @Sql;



/*   Execute query   */

EXEC sp_executesql @Sql;



DROP TABLE #TableA;

DROP TABLE #TableB;

You can execute dynamic query, in which you are replacing the aliases with the configured column names. You can do it with code like this:

use [tempdb]

go



drop table if exists TableA

drop table if exists TableB



create table TableA([Type] int, [Col1] int, [Col2] int, [Col3] int, [Col4] int, [Col5] int)



insert into TableA values

(101,      1,         2,         3,        2,         5),

(102,      4,         2,         3,        2,         0),

(103,      2,         1,         0,        0,         5),

(103,      7,         2,         0,        0,         5),

(105,      8,         3,         0,        0,         0)



create table TableB(ColID sysname, ColName sysname)



insert into TableB values

('Col1',         'Math'),

('Col2',         'English'),

('Col3',         'French'),

('Col4',         'Fine Arts'),

('Col5',         'Biology')





declare @sql nvarchar(max) = N'select [Type], [Col1] as [{Col1}], [Col2] as [{Col2}], [Col3] as [{Col3}], [Col4] as [{Col4}], [Col5] as [{Col5}] from TableA'



-- Execute unmodified query

exec sp_executesql @sql



-- Replace aliases with column names

select @sql = REPLACE(@sql, CONCAT(N'{', ColID, N'}'), ColName)

from TableB



-- Execute query with replaced aliases

exec sp_executesql @sql



-- Cleanup

drop table if exists TableA

drop table if exists TableB