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.

How to get rid of "New Notifications" window in Skype?

Many of you may have faced the problem in skype when your new notification window remains even after you read the message.

As for example, I have 5 to 6 people in the office with whom I have to chat everyday. But of late, I have seen the new notification window in my skype was not vanishing even after reading the message/event.

I found a solution for this.
a. Open your main Skype messenger window
b. Select the people name for whom you have the notification
c. Right click and choose "Mark as Read", it will go away.

Hope this post is useful for you.