Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

up vote 1 down vote accepted

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

share|improve this answer
    
Thank you. I have multiple fields besides Port and VIP1 in the user column. Is there a way that the query automatically adds on columns without me explicitely listing the two fields Port and VIP1? –  Rafey Aug 7 '13 at 14:17
add comment

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> 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 <-- 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

share|improve this answer
    
When I run this query, it gives me Vip1:invalid identifier. Also I am not trying to use case statements as I dont know the fields in the User column. –  Rafey Aug 6 '13 at 21:48
1  
I had edited the query a couple of times, you might have gotten an intermediate version. Edited now with explicit results in oracle. In the CASE version, you also explicitely list the two fields PORT and Vip1. Can you be more specific on what is not working? –  Ursula Aug 6 '13 at 22:11
    
Thank you! Its working now! I think I was using the unedited version. I have multiple fields besides Port and VIP1 in the user column. Is there a way that the query automatically adds on columns without me explicitely listing the two fields Port and VIP1? –  Rafey Aug 7 '13 at 14:15
    
I think the xml option would be best in my case. Can I create a view using this? –  Rafey Aug 8 '13 at 16:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.