SQL Server expression substitution in SELECT statements as column names

How do开发者_如何转开发 I evaluate a character expression to resolve to a valid column name in a SELECT statement that would return column row values? Eg valid column name = Customer_1 == 'Customer_'+'1'

You need to use dynamic SQL. An example

DECLARE @DynSQL nvarchar(max)

DECLARE @Suffix int = 1

SET @DynSQL = N'SELECT Customer_' + CAST(@Suffix as nvarchar(10)) + 
              N' FROM YourTable WHERE foo = @foo'

EXEC sp_executesql @DynSQL, N'@foo int', @foo=1

As always with dynamic SQL you need to consider SQL injection if any of the inputs to the process will be user supplied.

How do I evaluate a character expression to resolve to a valid column name in a SELECT statement that would return column row values? Eg valid column name = Customer_1 == 'Customer_'+'1'

You're probably doing something wrong if you need to do this, but if you have to: build the column names as rows and then pivot.