CODEWITHSUNDEEP
sqloracle-databaseoracle11g
mradul
mradul

Reputation: 550

Getting specified output using SQL

I have two table where data is like below -- These are sample tables

create table test_1 (Designation varchar2(500));
create table test_2 (Competencies varchar2(500));

insert into  test_1 values  ('Asst Manager - Accounts'  );         
insert into test_1 values ('Automation Lead'          );         
insert into test_1 values ('BSA Trainee'              );         
insert into test_1 values ('Business Analyst'         );         
insert into test_1 values ('Development Manager'      );         
insert into test_1 values ('Facility Supervisor'      );         
insert into test_1 values ('Head - Engineering'       );         
insert into test_1 values ('Head - Solutions Delivery');         
insert into test_1 values ('HR Executive' );                       
insert into test_1 values ('IT Consultant');
insert into test_1 values ('LAN support administrator');
insert into test_1 values ('Senior Manager, QA');
insert into test_1 values ('Manager, Technical Support');
insert into test_1 values ('Manager-HR');
insert into test_1 values ('Principal Architect');
insert into test_1 values ('Project Coordinator');
insert into test_1 values ('Project Lead');
insert into test_1 values ('Project Manager');
insert into test_1 values ('QA Consultant');
insert into test_1 values ('QA Engineer');
insert into test_1 values ('QA Lead');
insert into test_1 values ('QA Trainee');
insert into test_1 values ('Recruitment Executive');
insert into test_1 values ('Senior Development Manager');
insert into test_1 values ('Senior Manager - Administration / Procurement');
insert into test_1 values ('Senior Manager - Finance');
insert into test_1 values ('Senior Manager, QA');
insert into test_1 values ('Senior QA Engineer');
insert into test_1 values ('Senior Software Designer');
insert into test_1 values ('Senior Software Engineer');
insert into test_1 values ('Senior Technical Account Engineer');
insert into test_1 values ('Senior Technical Writer');
insert into test_1 values ('Software Engineer');
insert into test_1 values ('Software Trainee');
insert into test_1 values ('Solution Designer');
insert into test_1 values ('System Administrator');
insert into test_1 values ('Tech Lead');
insert into test_1 values ('Technical Account Engineer');
insert into test_1 values ('Technical Training Specialist');


insert into test_2 values  ( 'Adherence to processes');
insert into test_2 values  (  'Adherence to schedule and timelines');
insert into test_2 values  (    'Analytical Thinking');
insert into test_2 values  (   'Communication Skills');
insert into test_2 values  (     'Consistency');
insert into test_2 values  (   'Discipline (Epicor/ office timings/   responsiveness etc)');
insert into test_2 values  ( 'Initiative/ ownership/ acheivement    orientation');
insert into test_2 values  (  'Organizational activities (interviews/ trainings/ internal blogs etc)');
insert into test_2 values  ('Quality of deliverables');
insert into test_2 values  ('Self Development (what initiatives have you taken)');
insert into test_2 values  ('Team , peer, stakeholder relationships');
insert into test_2 values  ('Time Management');


select t1.Designation, t2.Competencies from test_1 t1, test_2 t2;

Now the output of this should be like

Desgnation                          Competencies
software engineer                list of all the competencies
Automation Lead                 list of all the competencies

and so on for all the designations like for all desgnations we need all competencies in another column.

Upvotes: 0

Views: 28

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Assuming the competency string is not too long, then you can use listagg(). I would recommend:

select t1.Designation,
       (select listagg(t2.Competencies, ', ') within group (order by Competencies)
        from test_2
       ) as competencies
from test_1 t1;

Upvotes: 1

Related Questions