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.

Answer 1

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