从字符串列名 sql 中获取列值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27522762/
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
Get column value from string column name sql
提问by Ancient
Is this possible to get multiple columns value when we have column name as string
Like if i have a table Testand i have columns FirstName , LastName , Address.
当我们将列名作为字符串时，这是否可能获得多列值，就像我有一个表Test并且我有列一样FirstName , LastName , Address。
Now what i want to get value of all three columns but i want to make this dynamic so that i just pass string column name i get values for that columns
现在我想获取所有三列的值，但我想使这个动态化，以便我只传递字符串列名我获取该列的值
Example
例子
Select
(select column_name from metadata )
from source table
回答by Sarath Avanavu
Pass the column names as parameters
将列名作为参数传递
DECLARE @COLS NVARCHAR(MAX)
DECLARE @TABLE NVARCHAR(MAX)
SET @COLS = 'COL1,COL2'
SET @TABLE = 'TABLENAME'
Now execute the query
现在执行查询
DECLARE @QRY NVARCHAR(MAX)
SET @QRY = 'SELECT (SELECT '+@COLS+' FROM '+@TABLE+') FROM sourcetable'
EXEC SP_EXECUTESQL @QRY
回答by danish
You can build the query in code dynamically. However it needs to be robust so that it does not gets prone to SQL injection. Something like this:
您可以在代码中动态构建查询。然而，它需要是健壮的，这样它就不会容易受到 SQL 注入的影响。像这样的东西：
string commandString = "select {0} from SomeTable";
SqlCommand command = new SqlCommand();
command.CommandText = string.Format(commandString, "selected column names");
command.EndExecuteReader();
In SQL:
在 SQL 中：
declare @query nvarchar(500)
set @query = replace('select 0 from author','0','column names from some parameter')
execute sp_executesql @query
Update 2: Does this do what you need?
更新 2：这是否满足您的需求？
declare @query nvarchar(500)
DECLARE @columnNames varchar(1000)
set @columnNames = ''
SELECT @columnNames = @columnNames + column_name + ',' FROM metadata
set @query = replace('select 0 from source_table','0',SUBSTRING(@columnNames,0,LEN(@columnNames)-1))
execute sp_executesql @query
回答by Sebri Zouhaier
You have to use dynamic SQL:
您必须使用动态 SQL：
http://www.sqlusa.com/bestpractices/dynamicsql/
http://www.sqlusa.com/bestpractices/dynamicsql/
Dynamic PIVOT also a consideration:
动态 PIVOT 也是一个考虑因素：
