Using two pivots while creating a view

From: Thomas Day <tomdaytwo_at_gmail.com>
Date: Wed, 14 May 2014 12:36:48 -0400
Message-ID: <CAMa2=-dCPH7n-34=C=pzFYqaUr4A2Beou6eJpi6UnMW=SYcwtw_at_mail.gmail.com>

I recently had to do this and couldn't find any examples for the correct syntax so I thought I'd post what worked for me. Oracle creates the view with no errors.

Create or replace view SOFTBALL_PIVOT_VIEW AS
(select * from
(select A.PERSON_ID PERSON_ID,

A.FIRST_NAME FIRST_NAME,
A.LAST_NAME LAST_NAME,
A.HOME_CITY CITY,
A.HOME_STATE STATE,
B.EMP_ID EMPLOYEE_ID,
B.DEPT_ID DEPARTMENT_ID,
C.FIRST_NAME DEPENDENT_FIRST_NAME,
C.LAST_NAME DEPENDENT_LAST_NAME,
C.AGE DEPENDENT_AGE

from PERSON A,
EMPLOYEES B,
FAMILIES C
where
A.PERSON_ID=B.PERSON_ID AND
A.PERSON_ID=C.HEAD_OF_FAMILY_ID
)
PIVOT (MAX(EMP_ID) AS EMPLOYEE_ID
,MAX(DEPT_ID) AS DEPARTMENT_ID
FOR (DEPT_ID) IN (1,2,3,4,5,6,7,8,9,10)) PIVOT(
MAX(C.FIRST_NAME||' '||C.LAST_NAME) AS DEPENDENT_NAME

FOR (DEPENDENT_AGE) IN (13,14,15,16,17,18,19,20,
21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,
41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)

));

--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 14 2014 - 18:36:48 CEST