How to make a particular field into seperate columns

dynamic-sqloracle-11g

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.

Best Answer

You were very close. What you forgot is to apply GROUP BY and an aggregate function (MIN() or MAX() it doesn't matter in this case) to each pivoted column.

That being said your query should look like

SELECT No, 
       MIN(CASE WHEN "User" = 'Port' THEN Value END) Port,    
       MIN(CASE WHEN "User" = 'Vip1' THEN Value END) Vip1  
FROM Table1 
 GROUP BY No

Output:

| NO |  PORT |      VIP1 |
--------------------------
|  1 | Funds |   Systems |
|  2 |  Bank | Authority |

Here is SQLFiddle demo

Related Question