cant get subquery to work 1

[bookouri]

Ive got a table something like

employee_identifiers

emp_pk id_desc id_type
-----------------------------------------
101 22222222 SSN
101 12345 DL
101 M Gender
101 509 height
101 185 weight




I want to create a view that gives me all of an employees identifiers
select * from v_emp_identifiers

emp_pk SSN DL Gender height weight
----------------------------------------------------
101 2222222212345 m 509 185
102 3333333313452 f 504 120

etc...

Ive been trying to do a subquery to do this, but I'm not having much luck.
Can somebody point me in the right direction.

 

[Dagon]

This is quite a common denormalisation problem. A subquery will not help in any way at all. The two approaches are either to do self-joins (but you would need to self-join the table 5 times) or to use an aggregation technique.

select emp_pk, 
       max(case when id_type = 'SSN' then id_desc end) as SSN,
       max(case when id_type = 'DL' then id_desc end) as DL,
       max(case when id_type = 'Gender' then id_desc end) as gender,
       max(case when id_type = 'Height' then id_desc end) as height,
       max(case when id_type = 'Weight' then id_desc end) as weight
from table
group by emp_pk

 

[SantaMufasa]

Sure, you are welcome to use my "matrix" function:

CREATE OR REPLACE FUNCTION matrix (query_in in VARCHAR2) RETURN VARCHAR2 IS
    incoming    varchar2(4000);
    hold_result varchar2(4000);
    c sys_refcursor;
Begin
    open c for query_in;
    loop
        fetch c into incoming;
        exit when c%notfound;
        hold_result := hold_result||','||incoming;
    end loop;
    return ltrim(hold_result,',');
END;
/

Function created.

col x heading "SSN, DL, Gender, Height, Weight" format a50
select emp_pk
      ,matrix('select id_desc from employee_identifiers where emp_pk = '||emp_pk) x
  from employee_identifiers
 group by emp_pk;

EMP_PK SSN, DL, Gender, Height, Weight
------ -------------------------------
   101 22222222,12345,M,509,185
   102 33333333,13452,f,504,120

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[bookouri]

thanks, guys, once again I was off on the wrong track and never would have got there without help..