Getting column names from a table and their values in multiple records
746386Jan 14 2010 — edited Jan 14 2010Hi every one.
I have an old table containing about 255 different columns.
UserID Option1 Option2 ... Option255
I need to return the information of one record (depending on the WHERE clause) into multiple records. In other words, I want the data to be returned that way:
Col1 Col2
UserID 12345
Option1 <somedata>
Option2 <somedata>
...
OptionN <somedata>
where Col1 and Col2 are the names of the columns in the returned recordset and UserID, Option1, Option2 and so on are the exact column names in the original tables.
I tried different aproches using pivots and crosstab but the point here is that I dont want to explicitly use 255 times the DECODE function. Options are being added to that table from time to time so the solution needs to be kind of dynamic by itself.
I've devellloped a stored procedure with a cursor reading the USER_TAB_COLUMNS table and then uses EXECUTE IMMEDIATE subqueries to read the real value in the user table (wich means 255 different access to that table.). I want something more efficient. Is a solution in one single query possible?
Can anyone help me with that please?
Edited by: user11282155 on Jan 13, 2010 9:14 PM