Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 88
  • Last Modified:

SQL Query

Hi All,

I'm time-pressured and stuck on a simple query.

Sample data:

drop table tab1 purge;
create table tab1(COL_A varchar2(50), COL_B varchar2(50), COL_C varchar2(50), R_NAME varchar2(10));

insert into tab1 values('X','Y','Z','RULE1');
insert into tab1 values('X','Y','Z','RULE2');
insert into tab1 values('X','Z','Z','RULE1');
insert into tab1 values('Y','Y','Z','RULE2');
insert into tab1 values('A','B','C','RULE3');
insert into tab1 values('A','B','C','RULE4');
insert into tab1 values('A','B','C','RULE1');
commit;

Open in new window


I need to return on the rows the distinct values of COL_A, COL_B & COL_V and return R_NAME in multiple Columns.

Therefore the output should look like this:

X | Y | Z | RULE1 | RULE2
X | Z | Z | RULE1
Y | Y | Z | RULE2
A | B | C | RULE3 | RULE4 | RULE1

Thanks,
Mark
0
SuperLight
Asked:
SuperLight
2 Solutions
 
Éric MoreauSenior .Net ConsultantCommented:
0
 
SuperLightAuthor Commented:
In my haste I should have made it clear that this is for an Oracle Database.

thanks,
Mark
0
 
sdstuberCommented:
you can't have a dynamic column list with a sql statement.

The columns to be returned must be known at the time the query is parsed (i.e. before it executes)
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
sdstuberCommented:
you can aggregate in oracle with the listagg function

select col_a,col_b,col_c,listagg(r_name,',') within group(order by r_name)
from tab1
group by col_a,col_b,col_c

but that puts them all into a single column.  if you want multiple columns, you must define what columns they will be and, since each row will have a variable number of results,  you must define how the columns should be populated since not every row will have enough values to fill the columns.
0
 
Abhimanyu SuriCommented:
SQL> select COL_A||'|'||COL_B||'|'||COL_C||'|'||listagg(R_NAME,'|') within group (order by COL_A,COL_B,COL_C) RESULT
  2  from asuri_admin.tab1
  3  group by COL_A,COL_B,COL_C
  4  /

RESULT
------------------------------------------------------------------------------------------------------------------------------------------------------
A|B|C|RULE1|RULE3|RULE4
X|Y|Z|RULE1|RULE2
X|Z|Z|RULE1
Y|Y|Z|RULE2

Provided order of RULE doesn't matter
0
 
SuperLightAuthor Commented:
Thanks v.much!

(so much too learn and so little time ;o))
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now