New Columns with Uniques

 

Hey Guys, 

 

This is the table when a user like a program it reads as follows:

 

User | Program
----------------
 A    |    Tennis
 A    |    Soccer
 B    |    Basketball
 B    |    Tennis
 B    |    Soccer


However I want to write a query that will allow me to have this new table. 

 

User | Tennis | Soccer | Basketball

-------------------------------------------

A     |   Yes     |  Yes     |   No

    |   Yes     |  Yes     |   Yes

 

 

For some reason this is what i am getting 

 

User | Tennis | Soccer | Basketball

-------------------------------------------

A     |   Yes     |   No      |   No

    |   No       |  Yes     |   No

B     |   No       |  No       |   Yes

    |   Yes     |  No       |   No

    |   No      | Yes       |   No

 

with this query - 


select User,
(Case when program like '%Tennis%' THEN 'Yes' END) Tennis,
(Case when program like '%Basketball%' THEN 'Yes' END) Basketball,
(Case when program like '%Soccer%' THEN 'Yes' END) Soccer
from table

 

 

Thanks!!!

 

 

Comments

  •  quick and dirty :) 

     

    with a as (
    SELECT Users,
    SUM(DECODE(program, 'Tennis', 1, 0)) "Tennis",
    SUM(DECODE(program, 'Basketball', 1, 0)) "Basketball",
    SUM(DECODE(program, 'Soccer', 1, 0)) "Soccer"
    FROM tbl
    GROUP
    BY Users
    )
    select Users,
    (Case when Tennis > 0 THEN 'Yes' else 'No' END) Tennis,
    (Case when Basketball > 0 THEN 'Yes' else 'No' END) Basketball,
    (Case when Soccer > 0 THEN 'Yes'else 'No' END) Soccer
    from a

     - use an agregate function so you can group by. then if you want the result with Yes and No embed the result set and transform the values. 

     

    - hope this helsp :) 

     

     there might be a better solution there 

  • just the way i like it ;)

     

    thanks

  • That did the trick :)

     

    happy to be helpful 

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file