来自存储在表中的值的 SQL 动态 SELECT 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5210180/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Dynamic SELECT statement from values stored in a table
提问by Matthew Dally
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.
我已经研究了几天，感觉自己在兜兜转转。我有 SQL 的基本知识，但有很多我不了解的领域。
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 SELECTstatement like the following but i am unable to get it work:
我想写一个SELECT像下面这样的语句，但我无法让它工作：
SELECT (SELECT [FieldName] FROM [TableName]) FROM tblFields
Is this possible? The application I have developed requires this for user customization of reports.
这可能吗？我开发的应用程序需要用户自定义报告。
采纳答案by Michael McMullen
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.
如果必须完全在 SQL 中完成，那么您需要告诉我们您使用的是什么数据库。如果是 SQL Server，您可能能够在第一个查询上使用游标来构建第二个查询，然后您使用 sp_executesql 存储过程执行该查询。但是建议在 SQL 之外执行此操作。
回答by Radu Caprescu
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:
如果我理解你想要做什么，我认为这会对你有所帮助。它并不漂亮，适用于 SQL Server 2005 及更高版本，但也许这就是您要寻找的：
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
编辑：正如马丁建议的那样，我进行了编辑，以便列和表名使用 QUOTENAME
