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







  • Options

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

  • Options

    just the way i like it ;)



  • Options

    That did the trick :)


    happy to be helpful 

Leave a Comment

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