New Columns with Uniques

Forum|Forum|10 years ago
February 19, 2016
3 replies
16 views

bluepomme
New Participant

Hey Guys,

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

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

+1

Adrian_Oprea_1
Participating Frequently
Forum|Forum|10 years ago
February 21, 2016

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