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

advertisements

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:

Table1:emp_Det

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

Table2:group

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
from
(select *
from emp_det
unpivot
(
  rating for group_name in ("PLSQL", "ADF")
)) t join "group" g on t.group_name = g.grp_name

Here is a sqlfiddle demo