Column to Row Conversion in Oracle

While working on your SQL queries, did you ever face the challenge of transposing your set of columns to a set of rows in your work? As in like changing the direction of a text "I Love my Family" from reading row-wise to reading column-wise (see screenshot below).

Then this post will definitely prove meaningful to you.

Solution

The crux behind the solution is to use rownum feature of a table, rownum inherently numbers the rows returned by a select query as 1,2,3,4 etc. For the sake of this particular problem, you can take any table from the list of your your database tables which has at least 4 records in it, but we will use this Oracle table:  USER_OBJECTS .

Run the following query to get a feeling

Select rownum as rown from user_objects   where rownum <= 10 ;

Building the set of queries for the problem straightway ...

Query1

 Select   'I Love My Family' Str  from  dual  ;

Query2

 Select  Substr ('I Love My Family', 1, instr('I Love My Family', ' ', 1, 1)-1) str1,

         Substr ('I Love My Family', instr('I Love My Family', ' ', 1, 1)+1, instr('I Love My Family', ' ', 1, 2)-instr('I Love My Family', ' ', 1, 1)-1) str2,

         Substr ('I Love My Family', instr('I Love My Family', ' ', 1, 2)+1, instr('I Love My Family', ' ', 1, 3)-instr('I Love My Family', ' ', 1, 2)-1) str3,

         Substr ('I Love My Family', instr('I Love My Family', ' ', 1, 3)+1) str4   

 from dual ;

Query3

Select   decode (rn, 1, str1, 2, str2, 3, str3, 4, str4) Str_Name  

 from  (

        Select  Substr ('I Love My Family', 1, instr('I Love My Family', ' ', 1, 1)-1) str1,

                Substr ('I Love My Family', instr('I Love My Family', ' ', 1, 1)+1, instr('I Love My Family', ' ', 1, 2)-instr('I Love My Family', ' ', 1, 1)-1) str2,

                Substr ('I Love My Family', instr('I Love My Family', ' ', 1, 2)+1, instr('I Love My Family', ' ', 1, 3)-instr('I Love My Family', ' ', 1, 2)-1) str3,

                Substr ('I Love My Family', instr('I Love My Family', ' ', 1, 3)+1) str4,

                rn   

         from  dual,

         (Select  rownum  rn  from  User_Objects  where rownum <=4)

    )    ;

If you run the above 3 queries in succession, you will understand the solution. "Rownum" is a really powerful tool for some serious sql query formation.

Hope this post comes meaningful to many of the sql-developers around.