1

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. – Aaron Bertrand Jun 1 '15 at 16:32
  • @AaronBertrand I didn't know that existed but it puts me on the right track. Thank you. – Matt Jun 1 '15 at 16:38
  • 4
    Please read this tip and this tip before putting any dynamic SQL into production. – Aaron Bertrand Jun 1 '15 at 16:41
2

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

DECLARE @columnList VARCHAR(MAX), @sql VARCHAR(MAX)
SELECT @columnList = COALESCE(@columnList+',' ,'') + columnName
FROM TableB

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

EXEC(@sql)
  • 2
    If I had access to the columnName column, I might be tempted to make it "null;drop table tableB;--" – Max Vernon Jun 1 '15 at 18:47
  • true dat......... – Queue Mann Jun 1 '15 at 19:22
1

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
('name'),('age'),('zipcode')

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  
        BEGIN


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

        FETCH NEXT FROM cr_columnlist INTO @columnname
        END

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 '15 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 '15 at 17:19
1
USE tempdb;
GO

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;--');

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = N'SELECT ' + STUFF((SELECT N',' + QUOTENAME([column])
  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;

Results:

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, privacy policy and cookie policy

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