Please read: PL/SQL 101 : Cursors and SQL Projection
which should give you an idea why doing that in SQL is not an easy thing to do (and is generally a bad idea anyway)
Also look at the FAQ: Re: 4. How do I convert rows to columns?
which describes ways of pivoting data.
It does look somewhat like your data is being stored in an Entity Attribute Value (EAV) type of model. They are notoriously poor data models for relational database engines, and should be avoided. (even as simply as considering the issues around storing numbers, strings, dates etc. in a single VARCHAR2 column... it's just wrong!)
If you are creating an EAV model database, take a read of the following, and it may change your mind: http://www.simple-talk.com/opinion/opinion-pieces/bad-carma/
1 person found this helpful
If your original columns are all the same datatype, then you can use UNPIVOT.
It looks like some of your columns are NUMBERS, but others are VARCHAR2s. In that case, you can use TO_CHAR to convert the NUMBERs to VARCHAR2s before you do the UNPIVOT, like this:
WITH string_data AS
SELECT TO_CHAR (deptno) AS deptno
UNPIVOT ( value
FOR variable IN (dname, deptno, loc)
LOC NEW YORK
Excellent!! Its working for me.
Thank You @Frank Kulash.
As said before it is not a good idea to store numers as strings but if you have to you can use the below:
WITH T AS
T UNPIVOT ( VAL FOR COL_NAME IN (B,C) )
T UNPIVOT ( VAL FOR COL_NAME IN (A,D) )