Oracle SQL Query Vertical to Horizontal with case

Question

i have user table which have 2 columns User and group , one user can have multiple groups

User  | Group
--------------
User1 | Group1
User1 | Group2
User2 | Group1
User2 | Group2
User3 | Group1
User4 | Group2

i am trying to write sql to convert above table data in below format.

User    | Group1        | Group2
---------------------------------
User1   |   Yes         |   Yes
User2   |   Yes         |   Yes
User3   |   Yes         |   No
User4   |   No          |   Yes

Need help. Thanks

Have you tried writing the query to get the result? You should edit the question with your attempts. — Taryn, Dec 20 '16 at 18:44

kjmerf · Answer 1 · 2016-12-20T18:59:19.327

0

I think you can do this with CASE and grouping:

SELECT user,
CASE WHEN MIN(`group`) = 'Group1' THEN 'Yes' ELSE 'No' END AS Group1,
CASE WHEN MAX(`group`) = 'Group2' THEN 'Yes' ELSE 'No' END AS Group2
FROM t
GROUP BY user

edited Dec 20 '16 at 18:59

answered Dec 20 '16 at 18:52

kjmerf

4,275
3
21
29

1

That wasn't included in the example. Probably worth a follow up question to the OP instead of a down vote on an answer... – kjmerf Dec 20 '16 at 19:14

Gurwinder Singh · Answer 2 · 2016-12-20T19:16:53.070

0

Try this:

select usr,
max(case when grp = 'Group1' then 'Yes' else 'No' end) grp1,
max(case when grp = 'Group2' then 'Yes' else 'No' end) grp2
from table
group by usr;

P.S.: Avoid keywords like USER in your tables.

edited Dec 20 '16 at 19:16

answered Dec 20 '16 at 19:02

Gurwinder Singh

38,557
6
51
76

This does not meet the requirement entirely - the OP's desired output shows `'No'` rather than `NULL` when the group is not found for that user. – Dec 20 '16 at 19:06

score 0 · Answer 3 · answered Dec 20 '16 at 19:16

with
     test_data ( usr , grp ) as (
       select 'User1', 'Group1' from dual union all
       select 'User1', 'Group2' from dual union all
       select 'User2', 'Group1' from dual union all
       select 'User2', 'Group2' from dual union all
       select 'User3', 'Group1' from dual union all
       select 'User4', 'Group2' from dual
     )
-- end of test data; the query begins below this line
select   usr,
         case when count(case when grp = 'Group1' then 1 end) > 0 
              then 'Yes' else 'No' end as grp1,
         case when count(case when grp = 'Group2' then 1 end) > 0 
              then 'Yes' else 'No' end as grp2
from     test_data
group by usr
order by usr    --   order by is optional
;

USR    GRP1  GRP2
-----  ----  ----
User1  Yes   Yes
User2  Yes   Yes
User3  Yes   No
User4  No    Yes

Oracle SQL Query Vertical to Horizontal with case

3 Answers3