I want to select specific columns from a table, let's say table A. Table B has the names of the names of the columns I would like to select. How would I write a MSSQL query that will use the distinct values from Table B as the column names that I want to query from table A?

  • You will need dynamic SQL. If you search for that you will find plenty of examples. Jun 1, 2015 at 16:32
  • @AaronBertrand I didn't know that existed but it puts me on the right track. Thank you.
    – Matt
    Jun 1, 2015 at 16:38
  • 4
    Please read this tip and this tip before putting any dynamic SQL into production. Jun 1, 2015 at 16:41

3 Answers 3


Can't you just use the COALESCE function like this?

SELECT @columnList = COALESCE(@columnList+',' ,'') + columnName

SET @sql = 'SELECT ' + @columnList + ' FROM TableA'

  • 2
    If I had access to the columnName column, I might be tempted to make it "null;drop table tableB;--"
    – Hannah Vernon
    Jun 1, 2015 at 18:47
  • true dat.........
    – Queue Mann
    Jun 1, 2015 at 19:22

The simplest way would be to use dynamic SQL. You would build your query string using Table B values and then execute it. Below is an example, if you would provide more details, ie table structures and sample data I can try to provide more detailed help.

Example 1 with fixed columns:

DECLARE @col1 varchar(25)
DECLARE @col2 varchar(25)
DECLARE @col3 varchar(25)
DECLARE @sqlcmd varchar(250)

SET @col1 = columnname from [dbo].[TableB] where id = 1
SET @col2 = columnname from [dbo].[TableB] where id = 2
SET @col3 = columnname from [dbo].[TableB] where id = 3

SET @sql = 'SELECT ' + @col1 + ',' + @col2 + ',' + @col3 + ' FROM dbo.TableA;'

EXECUTE (@sqlcmd);

Example 2 with dynamic columns (this includes sample tables and data:

CREATE TABLE tempdb.dbo.columnlist (columnname varchar(50))
INSERT INTO tempdb.dbo.columnlist (columnname) VALUES

CREATE TABLE tempdb.dbo.TableA (name varchar(25), age varchar(3), zipcode varchar(15))
INSERT INTO tempdb.dbo.TableA (name, age, zipcode) VALUES
('john', '10', '90210'),('jake', '103', '90210'),('jane', '85', '90210')

DECLARE @sqlcmd varchar(250)
DECLARE @columnname varchar(50)

DECLARE cr_columnlist CURSOR FOR 
SELECT DISTINCT columnname FROM tempdb.dbo.columnlist

SET @sqlcmd = 'SELECT '

OPEN cr_columnlist

FETCH NEXT FROM cr_columnlist INTO @columnname  
        WHILE @@FETCH_STATUS = 0  

        SET @sqlcmd = @sqlcmd + @columnname + ', '

        FETCH NEXT FROM cr_columnlist INTO @columnname

CLOSE cr_columnlist  
DEALLOCATE cr_columnlist

SET @sqlcmd = LEFT(@sqlcmd, LEN(@sqlcmd) - 1) -- Remove trailing comma and space

SET @sqlcmd = @sqlcmd + ' FROM tempdb.dbo.TableA;' -- Finish statement with FROM table

EXECUTE (@sqlcmd)
  • The problem with that query, for my specific application, is that the number of distinct items in table B will vary. I was thinking that I could put the items from table B into an array. Then, inside of a loop, I would declare a varchar and assign the current item. I'm hoping there is a simpler solution than that, however.
    – Matt
    Jun 1, 2015 at 16:54
  • Matt, I added a second example that uses a cursor to dynamically select column names. Please keep in mind that this should be tested thoroughly if it will be used in a production application.
    – jclabonde
    Jun 1, 2015 at 17:19
USE tempdb;

CREATE TABLE dbo.TableA(col1 INT, col2 INT, col3 INT);

CREATE TABLE dbo.TableB([column] NVARCHAR(128));
INSERT dbo.TableB([column]) VALUES(N'col1'),(N'col3'),
  (N'fake column'),(N'x;DROP TABLE dbo.BobbyTables;--');


  FROM dbo.TableB 
  WHERE [column] IN 
    SELECT name FROM sys.columns 
    WHERE [object_id] = OBJECT_ID(N'dbo.tableA')
  FOR XML PATH, TYPE).value(N'.[1]','nvarchar(max)'),1,1,N'')
  + N' FROM dbo.TableA;';

PRINT @sql;
EXEC sys.sp_executesql @sql;


SELECT [col1],[col3] FROM dbo.TableA;
-- note that fake column wasn't included,
-- nor was a DROP TABLE attempted.

Some background reading to address potential issues (including those suggested in other answers):

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.