New Columns with Uniques

bluepomme

 

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

 

 

Adrian_Oprea_1

 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 

bluepomme

just the way i like it

 

thanks

Adrian_Oprea_1

That did the trick

 

happy to be helpful