Rolling up multiple rows into one in SQL

How do I roll the values (preferably using PL/SQL) in different columns for same person A into one column?

I want to convert this:

NAME SUB1 SUB2 SUB3 SUB4
A PASS
A PASS
A PASS
A PASS


into :

NAME SUB1 SUB2 SUB3 SUB4
A PASS PASS PASS PASS


I tried to use 'stuff' funciton but that would only be good to club all values under one field. Is there a way to do this?

1 Answer
1

Bunch of MAXs does the job (at least, according to what you posted so far).

SQL> with test (name, sub1, sub2, sub3, sub4) as
2 (select 'a', null, null, null, 'pass' from dual union all
3 select 'a', null, null, 'pass', null from dual union all
4 select 'a', null, 'pass', null, null from dual union all
5 select 'a', 'pass', null, null, null from dual
6 )
7 select name, max(sub1) sub1, max(sub2) sub2, max(sub3) sub3, max(sub4) sub4
8 from test
9 group by name;

N SUB1 SUB2 SUB3 SUB4
- ---- ---- ---- ----
a pass pass pass pass

SQL>


By the way, did you actually mean to say "preferably using SQL"? Why would you involve PL/SQL?

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.