SQL set column names from another query

Question

I'm am using MS Reporting Services to graph some data from an Oracle database. I want to name the columns in my select statement with values from another select statement. Is this possible?

Like instead of "Select Column1 As 'Test' From Table1" could I do something like "Select Column1 As (Select column2 from Table2 where Value = 1) From Table1"?

Answer 1

I would think you'd have to query out separately, then form the query dynamically. Interested to see if there is a different answer.

Answer 2

My PL/SQL's a little rusty, so what follows is more pseudocode than compilable & tested code. And this is completely off the top of my head. But if you know the specific ordinal location of the column in the table, you may try this:

columnName varchar2(50) :=

Select column_name
From all_tab_columns c 
Where lower(table_name) = '<% Your Table2 Name %>' And
    column_id = 9 -- The appropriate ordinal
Order By column_id;

Select Column1 As columnName From Table1;

There may be more column values drawn from "all_tab_columns" that'll help you as well. Take a look around and see.

I hope this helps.

Answer 3

You can query all needed column names into separate report dataset, create hidden multivalue report parameter vColumns, set dataset with columns as a parameter default values, and use it as a string array:
Parameters!vColumns(0).Value - will be the first column etc. So you can use them as a query parameters.

See Lesson 4: Adding a Multivalue Parameter