Specify the column names in the select clause using a query

I have a S开发者_如何学CQL query where I want to specify the names of the columns dynamically.

Let's say I have a table called TABLE_A, and it has a column by name ID. I was wondering if I could do something like:

 SELECT (SELECT 'ID' 
           FROM DUAL) 
   FROM TABLE_A

Obviously this is not possible. Is there a better way to this?

---

SQL does not support dynamic column or table names -- you need to use dynamic SQL to get the functionality you want. Dynamic SQL means constructing a string, concatenating as necessary, before submitting the string (containing the query) to the database for interpretation.

Dynamic SQL is supported by most databases, but the syntax is often very different. Oracle provides:

• EXECUTE IMMEDIATE
• using an implicit cursor

This link provides examples of both.

Lest we forget Little Bobby Tables, dynamic SQL is an increased risk of SQL injection attacks...

---

You could use dynamic SQL if you are in a PL/SQL environment.

Build your SQL string as a VARCHAR2 before executing it.

DECLARE
  v_sql VARCHAR2(4001);
  v_column VARCHAR2(30) := 'whatever';
  v_sql_result VARCHAR2(4001);
BEGIN
  v_sql := 'SELECT '||v_column||' FROM table_a';

  EXECUTE IMMEDIATE v_sql
  INTO v_sql_result;
EXCEPTION
  WHEN ...
  THEN
      ...
END;

This will select the contents of column "whatever" into the v_sql_result. Of course I ommitted the WHERE clause to ensure only one row was returned for this example but you can add that yourself or lookup how EXECUTE IMMEDIATE works in Oracle.

---

If you want a dynamic list of columns, you might be better off using dynamic sql. I try to avoid it whenever I can, but this is a prime example of when to use it.

example:

DECLARE @sqlQuery varchar(300)

SET @sqlQuery = 'select '

------ logic loop happens ----- SET @sqlQuery = @sqlQuery + 'columnname, ' ------ end loop ------

SET @sqlQuery = @sqlQuery + ' from TABLE_A where '

exec(@sqlQuery)

It's at least a place to start for you.