Clever Geek Handbook

Get column value from sql row column name

Is it possible to get multiple column values ​​when we have a column name as a string For example, if I have a table Test

and I have columns FirstName , LastName , Address

.

Now when I want to get the value of all three columns, but I want to make this dynamic to just pass the column name of the row, I get the values ​​for those columns

Example

Select 

(select column_name from metadata )

from source table
+3


source to share


3 answers


Pass column names as parameters

DECLARE @COLS NVARCHAR(MAX)
DECLARE @TABLE NVARCHAR(MAX)
SET @COLS = 'COL1,COL2'
SET @TABLE = 'TABLENAME'


Now run the query

DECLARE @QRY NVARCHAR(MAX)
SET @QRY = 'SELECT (SELECT '+@COLS+' FROM '+@TABLE+') FROM sourcetable'
EXEC SP_EXECUTESQL @QRY
+5


source


You can build your query dynamically in code. However, it must be reliable so that it is not susceptible to SQL injection. Something like that:

string commandString = "select {0} from SomeTable";
SqlCommand command = new SqlCommand();
command.CommandText = string.Format(commandString, "selected column names");
command.EndExecuteReader();

In 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?

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
+1


source


You have to use dynamic SQL:

http://www.sqlusa.com/bestpractices/dynamicsql/

Dynamic PIVOT also takes into account:

http://www.sqlusa.com/bestpractices2005/dynamicpivot/

0


source






More articles:


All Articles