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







  •  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 

  • just the way i like it ;)



  • 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

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