SQL Dynamic SELECT statement from values stored in a table

Question

I have been researching this for a couple of days and feel like I am going around in circles. I have basic knowledge of SQL but there are many areas I do not understand.

I have a table that stores the names and fields of all the other tables in my database.

tblFields
===================================================

TableName      FieldName     BookmarkName  
---------------------------------------------------
Customer       FirstName     CustomerFirstName  
Customer       LastName      CustomerLastName  
Customer       DOB           CustomerDOB  

I want to write a SELECT statement like the following but i am unable to get it work:

SELECT (SELECT [FieldName] FROM [TableName]) FROM tblFields

Is this possible? The application I have developed requires this for user customization of reports.

Solution 1

If I understand correctly what you are trying to do, you are probably better off doing this as two separate queries from your program. One which gets the fields you want to select which you then use in your program to build up the second query which actually gets the data.

If it must be done entirely in SQL, then you will need to tell us what database you are using. If it is SQL Server, you might be able to user a cursor over the first query to build up the second query which you then execute with the sp_executesql stored procedure. But doing doing it outside of SQL would be recommended.

Solution 2

If i understand what you are trying to do, i think this will help you. It is not pretty and it works for SQL Server 2005 and above, but maybe this is what you are looking for:

declare @tableName nvarchar(100)
declare @sqlQuery nvarchar(max)
declare @fields varchar(500)
set @tableName = 'YourTableName'
set @fields = ''
select @fields = @fields + QUOTENAME(t.fieldname) + ',' from (
select distinct fieldname from tblfields where tablename = @tableName)t


set @sqlQuery = 'select ' + left(@fields, LEN(@fields)-1) + ' from ' + QUOTENAME(@tableName)

execute sp_executesql @sqlQuery

Edit: As Martin suggested, i edited so that the columns and tablename are using QUOTENAME