The definitive Oracle COLUMNS TO ROWS solution

Need your Oracle query that returns columns (fields) of data to instead return rows (like key/value pairs)? Don't want to hard-code every single column name as you need to do in the commonly suggested methods(max/decode and pivot)?

You've come to the right place. I will show you how and it's crazy simple.

In my case, and probably yours, you have a query result such as this:

colname1   colname2   colname3
data            data            data

And you need it like this:

Attribute    Value
colname1   data
colname2   data
colname3   data

So did I. And I did exactly what you're doing. I scoured my books. Pivot or max/decode. I Googled till my fingers bled. Pivot or union all. More Googling. A table function example. Too heavy.

In most, if not all, cases stating that you needed to hardcode column values of your query.

I didn't want to do that as I knew the project where I needed this type of query result would change. And I didn't want to crack open a package or hard code another column in somewhere when it did.

In the middle of writing a table function, at the ends of desperation, I googled one final time.

And found. The answer.

XML.

Oracle's provided XML functions would allow me to transform my result set into XML, and in doing so, allow me to use XPATH to be able to discern both column name and value from each node (record) returned.

The solution to all your troubles my friend is below for your enjoyment, plain and simple. You may need to brush up a bit on your XPATH syntax, but having the below query @ your disposal takes care of 90% of your problems.

SELECT 
X.ATTR
,X.VAL
FROM XMLTABLE ('ROWSET/ROW/*' PASSING
DBMS_XMLGEN.GETXMLTYPE('
SELECT dummy AS example_col1, dummy AS example_col2, dummy AS example_col3 from dual where rownum < 2')  
COLUMNS
ATTR VARCHAR2(4000) PATH 'name()', VAL VARCHAR2(4000) PATH 'data(.)'
) X


So if you need it, allow me to explain, briefly, what's going on above.

1. First you have YOUR query highlighted in yellow. If your query returns a result, you can use it here. To make it simple to read in my real world project, I used a view to hide most of the major SQL and was able to simply write 'select * from my_view' in the high lighted area above. In the simple example above, you'd generally want one record returned so you'd usually add some sort of predicate specifying a key, etc.
2. Your query is returned as XML at which time you use the awesome Oracle provided function XMLTABLE to query from that XML fragment as if it were a sql result set.
3. You'll notice the hard-coded 'ROWSET/ROW/*' specification. That tells the XMLTABLE to work on every child node of ROW returned. You can adjust this search path to return more/less specific areas of your result set. (Oracle returns your query using DBMS_XMLGEN.GETXMLTYPE with ROWSET as the main XML node and ROW as the node housing each record.)

I hope this example reaches you before you've written that pivot query or table function as it provides a tremendously flexible, yet amazingly simple (10 lines of code up there - and I broke it up a bit ;)), solution to get your columns into the rows you need to loop thru.

Please keep in mind that the example I give is extremely simple just to demonstrate the point. My final query involved a much more complex example which allowed me to not only return a key/value pair, but also return an 'id' column for the returned rows allowing me to no longer need the 'key' in the xml'd sql query as suggested in step 1 above. As such, I could turn the column to row sql into a view where I could then get all records returned and then filter by the key after.