Geek Answers Handbook

Is it possible to have dynamic selection columns defined by a subquery?

for instance

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

It returns col_name instead of table.col_name, for example, if col_name is x1, then the above select will return "x1" instead of SELECT x1 FROM my_table

Is there any way to do this in

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

If so, how can I use select select columns in the where clause without repeating the subheading?

+4
source share
4 answers

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)
+2
source

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

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

Not quite what you are looking for, but still interesting 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 convert the resulting columns to XML, and then remove the tag tags from it; Thus, this sample will give you the following result.

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

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

I think you could use PIVOT in 11g to convert it back to a single line, but I don’t have the corresponding instance manually, so I can’t say for sure.

+1
source

More articles:


All Articles