Concatenate strings to specify field name

Using MS SQL Server but porting on MySQL so I need a solution for both ideally (but MySQL preferably)

I am trying to concatenate two field in order to specify the field name for the select statement.

Example table:

id     | name
1      | John
2      | Bob


SELECT id, 'na'+'me' as value FROM table WHERE 1=1

actual return (of course)

id     | value
1      | name
2      | name

expected return:

id     | value
1      | John
2      | Bob

I have n开发者_StackOverflowo clue as to how to specify that the concatenate result is to be used as field name, and not as result.

I'm confused by your question but the query you need to get the expected return is simply:

SELECT id, name as value FROM Table

This will work in both mySQL and SQL Server

UPDATE:

It just occured to me that you might be talking about specifying which column to select using concatenated values. To do this in SQL Server use dynamic SQL. Example:

EXEC('SELECT id, ' + 'na' + 'me' + ' as value FROM table WHERE 1=1')

The above would be pointless if that's how you actually plan on using it. If na and me are parameters or something this would make sense. Be sure to read this article before using Dynamic SQL in SQL Server.

MySQL Version:

mysql> set @sql_text := 'SELECT id, ' + 'na' + 'me' + ' as value FROM table WHERE 1=1';

mysql> prepare stmt from 
    -> @sql_text

mysql> EXECUTE stmt

Lifted from here.

Sounds like you are trying to dynamically generate SQL with SQL? If so, use your SQL to generate a valid SQL command and then use the EXEC command on it. (MSDN documentation for T-SQL EXEC)

DECLARE @SQL varchar(max)
SET @SQL = 'SELECT id, ' + 'na' + 'me' + ' as value FROM table WHERE 1=1'
EXEC (@SQL);

If you need to generate from data, something like:

DECLARE @SQL varchar(max)
SET @SQL = 'SELECT id, ' + field1 + field2 + ' as value FROM table WHERE 1=1' FROM sometable WHERE 1=1
EXEC (@SQL);

This is generally discouraged for security. Depending on how you're getting the data to generate the SQL it could allow for SQL injection attacks. Use with caution.