How to Select Headers dynamically from another table

Question

Post reply

How to Select Headers dynamically from another table

Sangeeth Raj

Hall of Fame

Points: 3541
More actions
January 16, 2020 at 8:49 am

#3715899

Hi,
From the following script, I am able to choose one column rows from #TableB to columns Headers to #TableA. But I would like to dynamically choose which column rows needs to be select from #TableB

CREATE TABLE #TableA (Type int, Col1 int, Col2 int, Col3 int, Col4 int, Col5 int);
CREATE TABLE #TableB (ColId varchar(50), EnglishColumnName varchar(50),SpanishColumnName nvarchar(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','Matemáticas'),
('Col2', 'English','inglés'),
('Col3', 'French','Francés'),
('Col4', 'Fine Arts','Bellas Artes'),
('Col5', 'Biology','Biología');
SELECT * FROM #TableA
SELECT * FROM #TableB
DECLARE @sql nvarchar(MAX);
/* Build SELECT */
SET @sql = 'SELECT ';
SELECT @sql = @sql + ColId + ' AS ''' + EnglishColumnName + ''', ' FROM #TableB; ---- I want to pass EnglishcolumnName here as Variable, instead of Hardcoding here. is it possible.
SELECT @sql
/* 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;

I would like to choose whether do I need to see #TableA Columns either EnglishColumnName or SpanishColumnName from Table#B.

I really appreciate for your time.

Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27055 More actions · Answer 1

Duplicate post:

https://www.sqlservercentral.com/forums/topic/dynamically-choose-columns-names

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

Most people read more than one of the topics (or, like I do, look at which topics are new), so posting a question multiple times just leads to different answers in different places, duplicating effort and causing confusion. Thanks!

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Sangeeth Raj Hall of Fame Points: 3541 More actions · Answer 3

Hi,

My apologies for duplicate post, Initially I have posted this thread in 2019, then I have realized it might be in-appropriate to post in 2019 forum when I am working in 2016. Hence I have posted in 2016.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

It's not a problem in any way. Just trying to be helpful. You'll get a better, more consistent response this way.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning