how to make a particular field into seperate columns? Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production. 

I have this table in the below format: 


No | User | Value
01 | Port | Funds   
01 | Vip1 | Systems  
02 | Port | Bank  
02 | Vip1 | Authority    


This is how I want it:


No | Port  | Vip1
01 | Funds | Systems   
02 | Bank  | Authority


Now the problem is, in this table, the User column has 6 other entries besides Port and Vip1. So I would want 6 columns and their respective values. I also want a query that I can use in other similar tables that have different User column entries. This is what I was trying to do without any success:  

SELECT 
   No, 
   CASE user WHEN 'Port' THEN Value ELSE NULL END AS Port,    
   CASE user WHEN 'Vip1' THEN Value ELSE NULL END AS Vip1  
FROM table1  


Please let me know what you think.
This query gives you the answer you want including "NULL" values if for a given No one of the Usernames is null. 

SELECT  t1.NO No, 
        t1.VALUE AS PORT, 
        t2.VALUE AS Vip1 
FROM table1 t1 FULL OUTER JOIN table1 t2 
     ON t1.NO = t2.NO 
WHERE t1.USERNAME = 'Port' AND t2.USERNAME = 'Vip1';


On the table:

SQL&gt; select * from table1 where username = 'Port';

NO USERNAME             VALUE
-- -------------------- ----------------------------------------
01 Port                 Funds
02 Port                 Bank


I changed the column "USER" to "USERNAME", because "USER" is a reserved word in oracle. 

This gives the output (in oracle 11g):

No PORT VIP1
-----------------
01 Funds Systems
02 Bank Authority


Another way to achieve this is a PIVOT table. A caveat is, that PIVOT tables only work with aggregate functions. For example, if you had two different entries for "Port" for No = 01, this would only return one of them. 

SELECT * FROM 
    (SELECT No, Username, VALUE from table1) 
    PIVOT (MAX(Value) 
           FOR (Username) 
           IN ('Vip1', 'Port')) 
ORDER BY No;


A slight variation of this gives you a pivot table for unspecified Usernames. 
Unfortunately, oracle11g only provides XML output for this. 

 SET LONG 10000 &lt;-- to see the output on the terminal
 SELECT * FROM (
    SELECT No, Username, VALUE FROM table1) 
    PIVOT XML (MAX(Value) 
               FOR (Username) 
               IN (SELECT DISTINCT Username FROM table1));


For more information on oracle pivot tables see http://www.oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1.php