Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

B     |   Yes     |  Yes     |   Yes

 

 

For some reason this is what i am getting 

 

User | Tennis | Soccer | Basketball

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

A     |   Yes     |   No      |   No

A     |   No       |  Yes     |   No

B     |   No       |  No       |   Yes

B     |   Yes     |  No       |   No

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.