Joining Different Tables with one table coumn name and another table's coumn values

Question

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

Answer 1

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

Answer 2

Try something like this (not completly sure about the syntax):

SELECT emp_id, emp_name, grp_id, grp_name, plsql AS rating
  FROM emp_det, group
 WHERE grp_name = 'PLSQL'
UNION
SELECT emp_id, emp_name, grp_id, grp_name, adf AS rating
  FROM emp_det, group
 WHERE grp_name = 'ADF';