Join different tables with a table column name and column values ​​from another table


I am new for oracle. here i have created two table as follows: Table 1:

create table emp_Det(emp_id varchar2(30), emp_name varchar2(80), PLSQL varchar2(20), ADF varchar2(20));

Table 2:

create table group(grp_id varchar2(30), grp_name varchar2(80));

I have inserted values in both tables as follows:


Emp_id    Emp_name   PLSQL    ADF
Sample1    Sample1     5       5
Sample3    Sample2     5       5
Sample3    Sample3     5       5


grp_id      grp_name
ORA          PLSQL
ORAS         ADF

We have to select columns from these two tables and have to join one table's column name with another table's column value. the columns names are emp_id,Emp_name,Grp_id,Grp_name,rating.

for example, In first table(EMP_DET) "PLSQL" is column name and "PLSQL" is a value of Second table(Group) column grp_name and result should come as follows

EMP_ID   EMP_NAME    GRP_ID     GRP_NAME    Rating
Sample1   Sample1     ORA        PLSQL         5
Sample1   Sample1     ORAS       ADF           5

please help me out to prepare query or procedure or suggest me

If you're using 11g and above then you can try this:

select t.*, g.grp_id
(select *
from emp_det
  rating for group_name in ("PLSQL", "ADF")
)) t join "group" g on t.group_name = g.grp_name

Here is a sqlfiddle demo