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 am glad for your time
Thanks
January 16, 2020 at 1:37 pm
Honestly, this is work I would put on to the front-end. There are ways to do translations. Otherwise, you're going to have to do pretty horrible stuff to your T-SQL code.
So, the only way to do this that I can think of would be to pass in a parameter to the query that determines which language you're going to use. Then, go to the table that has the column names and load all that into variables. Then, use those variables to build your sp_executesql string.
Doing this for one query will be a pain. Doing this for every query will probably prove prohibitively difficult. There's no way to do this within T-SQL. You can define the collation per column, but it won't change the alias.
Strong recommendation, don't try this.
"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
January 16, 2020 at 3:58 pm
Maybe use a separate view for each language you need. The view itself would be static but would be generated dynamically.
If you want to try this approach, and want further details, just let us know.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Hi,
@scottpletcher, @Grant Fritchey: I am really glad for your messages, I have figured it out how to handle this scenario as follows
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 @WhichLanguageColumnName varchar(30)
SET @WhichLanguageColumnName = 'SpanishColumnName' --we can pass this as parameter in proc --
DECLARE @tab table (colId varchar(30), ColumnName nvarchar(40))
DECLARE @SelectLanguage nvarchar(MAX);
DECLARE @Sql nvarchar(MAX);
SET @SelectLanguage = 'Select ColId, ' + @WhichLanguageColumnName + ' from #TableB'
INSERT INTO @tab
EXECUTE sp_executesql @SelectLanguage
SELECT @sql = 'Select '
SELECT @sql = @sql + ColId +' AS ''' + ColumnName + ''', ' FROM @tab
SELECT
@SQL=LEFT(@SQL,LEN(@SQL)-1)
SELECT @sql = @sql + 'FROM #TableA'
EXECUTE sp_executesql @sql
January 16, 2020 at 7:19 pm
This should put you in the ball park...
DECLARE
@Language char(1) = 'S',
@sql nvarchar(MAX) = N'';
SELECT
@sql = CONCAT(@sql, ', ', ISNULL(CASE WHEN @Language = 'E' THEN CONCAT(QUOTENAME(ISNULL(b.EnglishColumnName, c.name)), ' = a.', c.name) WHEN @Language = 'S' THEN CONCAT(QUOTENAME(ISNULL(b.SpanishColumnName, c.name)), ' = a.', c.name) END, c.name))
FROM
tempdb.sys.columns c
LEFT JOIN #TableB b
ON c.name = b.ColId
WHERE
c.object_id = OBJECT_ID('tempdb..#TableA')
SET @sql = CONCAT(N'
SELECT
', STUFF(@sql, 1, 2, ''), N'
FROM
#TableA a');
EXEC sys.sp_executesql @sql;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply