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.

Indeterminable

I don't understand what you want to do. – Biblical

You could use a string constructor to create this query - though possibly it would have to use another language (e.g. C#) to construct the query, and then run it against the DB. – Entomb

You need to state RDBMS (and version). – Dimercaprol

Answer 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.

Zagreus

Hi Michael, thanks for your answer. After I posted the question I went away and tinkered with the problem for a while, but your answer is basically what I came up with. – Indeterminable

I have a bookmarks table which lists each of the fields in the database and a relevant bookmark name. I have a second table which lists all the bookmarks found in a Word document that I want to replace with the database values. I am using a stored procedure that generates XML code from the two tables containing the bookmark names and values which I then use to update the Word document. – Indeterminable

Answer 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

Aquifer

You should use QUOTENAME for the column and table names. – Dimercaprol

Hi Radu, thank you for your answer. Like Michael, your answer was basically what I came up with. I marked Michael's as correct as he answered first. :) – Indeterminable