SQL & PL/SQL :: Data Transformation / Remodel Some Data For Processing

I am trying to remodel some data for processing, as unfortunately the original data structure is unsuitable.

My requirements are to recreate the data as a view which can then be query'd directly by the client, and therefore would like to encapsulate all the required transformation within one SQL query.Here is a sample of the table structure i'm faced with:

SQL>
SQL> l
1 create table insane
2 (
3 dept number not null,
4 firstname varchar2(30),
5 surname varchar2(30),
6 city varchar2(30),
[code]....

So, i'd like to be able to turn this on it's head and come out with data that looks something like this (which, for the purpose of this example, is fabricated manually):

SQL> select * from sane
2 /

DEPT_ID PERSON_ID FIRSTNAME
---------- ---------- ------------------------------
SURNAME CITY
------------------------------ ------------------------------
1 30 Peabo
[code]....