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
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.