Another Dynamically alias column question

Question

I might be asking the same question as this, but perhaps if I ask it my way, a different solution may become obvious. I have this:

DECLARE @Table1 AS TABLE(
Table1ID Int Primary Key
,ColumnName varchar(max)
)
INSERT INTO @Table1(Table1ID,ColumnName) VALUES(1000,'A')
INSERT INTO @Table1(Table1ID,ColumnName) VALUES(2000,'B')
DECLARE @Table2 AS TABLE(
Table2ID Int Identity Primary Key
,Table1ID Int
,FieldData varchar(max)
)
INSERT INTO @Table2(Table1ID,FieldData) VALUES(1000,'A DATA')
INSERT INTO @Table2(Table1ID,FieldData) VALUES(2000,'B DATA')
SELECT * FROM @Table1
SELECT * FROM @Table2
SELECT FieldData AS A 
from @Table2
WHERE Table1ID = 1000
SELECT FieldData AS B
from @Table2
WHERE Table1ID = 2000

And I want to do this:

SELECT FieldData AS [Table1.ColumnName]
from @Table2
WHERE Table1ID = 2000

score 3 · Answer 1 · edited May 23 '17 at 12:40

If you can use #temp tables instead of table variables, this becomes dead easy.

CREATE TABLE #Table1
(
 Table1ID Int Primary Key,
 ColumnName varchar(max)
);

INSERT INTO #Table1(Table1ID,ColumnName) 
  VALUES(1000,'A'),(2000,'B');

CREATE TABLE #Table2
(
  Table2ID Int Identity Primary Key,
  Table1ID Int,
  FieldData varchar(max)
);

INSERT INTO #Table2(Table1ID,FieldData) 
  VALUES(1000,'A DATA'),(2000,'B DATA');

Now, I can do this:

DECLARE @sql NVARCHAR(MAX), @Table1ID INT = 2000;

SELECT @sql = N'SELECT FieldData AS ' + QUOTENAME(ColumnName) 
  + N' FROM #Table2 WHERE Table1ID = @Table1ID;'
FROM #Table1 WHERE Table1ID = @Table1ID;

EXEC sys.sp_executesql @sql, N'@Table1ID INT', @Table1ID;

Results:

B
------
B DATA

If you need to use table variables for some reason (as explained in an excellent answer by Martin Smith, they're actually usually less desirable than #temp tables, or indifferent even in ways where most people make assumptions that table variables are better), then you can use a really fugly hack based on this answer (also by Martin Smith, coincidentally).

First, you need to create a table type in your database (and creating a copy of this in model may be a good idea as well, because in some scenarios it may need to also exist in tempdb).

CREATE TYPE dbo.Table2 AS TABLE
(
  Table2ID Int Identity Primary Key,
  Table1ID Int,
  FieldData varchar(max)
);

Now, your code would be:

DECLARE @Table1 AS TABLE
(
  Table1ID Int Primary Key,
  ColumnName varchar(max)
);

DECLARE @Table2 AS dbo.Table2;

INSERT INTO @Table1(Table1ID,ColumnName)
 VALUES(1000,'A'),(2000,'B');
INSERT INTO @Table2(Table1ID,FieldData)
 VALUES(1000,'A DATA'),(2000,'B DATA');

DECLARE @sql NVARCHAR(MAX), @Table1ID INT = 2000;

SELECT @sql = N'SELECT FieldData AS ' + QUOTENAME(ColumnName)
  + N' FROM @Table2 WHERE Table1ID = @Table1ID;'
FROM @Table1 WHERE Table1ID = @Table1ID;

EXEC sys.sp_executesql @sql, 
  N'@Table2 dbo.Table2 READONLY, @Table1ID INT',
  @Table2 = @Table2, @Table1ID = @Table1ID;

Results, again:

B
------
B DATA

From a simplicity (and portability / forward compatibility) perspective, I would argue for the former solution where you just use #temp tables instead of table variables and a TVP.

Another Dynamically alias column question

1 Answers1