问题描述:

For example

SELECT (SELECT col_name FROM column_names WHERE col_id = 1) FROM my_table

It returns the value of col_name instead of the value of table.col_name

e.g. if col_name is x1 then the above select will return "x1" instead of the value of SELECT x1 FROM my_table

Is there a way to do it in

  • Microsoft SQL Sever 2008? (based on the answers, seems that yes)
  • Oracle 11g?

If it is, how can I use the select clause columns in a where clause without repeating the subselect?

网友答案:

In SQL Server you can use dynamic SQL, something like this:

declare @TableName sysname = quotename('Test')

declare @ColumnList varchar(max)

select @ColumnList = isnull(@ColumnList + ', ', '') + quotename(name)
from sys.columns
where object_name(object_id) = @TableName

declare @SqlCommand varchar(max) = 'select ' + @ColumnList + ' from ' + @TableName 
execute(@SqlCommand)
网友答案:

No it is not possible, you need to write Dynamic sql for it and need to use "Execute" command for executing the query.

网友答案:
DECLARE @column nvarchar(100), @query nvarchar(max)
SET @column = (SELECT [col_name] FROM column_names WHERE col_id = 1)
SET @query = 'SELECT ' + QUOTENAME(@column) + ' FROM [my_table]'
EXEC sp_executesql @query
网友答案:

Not exactly the thing you're looking for, but still interesting one for Oracle.

WITH t AS
 (SELECT 'one' column_one, 'two' column_two, 'three' column_three FROM dual)
SELECT XMLTYPE(EXTRACT(VALUE(T), '/*').GETSTRINGVAL()).GETROOTELEMENT() VALUE
  FROM TABLE(XMLSEQUENCE(XMLTYPE((CURSOR (SELECT * FROM t))).EXTRACT('/ROWSET/ROW/*'))) T;

Basically, you transform your resulting columns into an XML, and then parse tag names out of it; thus, this sample will yield you the following output.

VALUE
-----------
COLUMN_ONE
COLUMN_TWO
COLUMN_THREE

If your original query returns more than one row, then you will have your above result set multiplied by the number of rows.

I believe you could use PIVOT in 11g to transform it back into single row but I haven't got corresponding instance by hand so I can't tell for sure.

相关阅读:
Top