Using two pivots while creating a view
- From: Thomas Day <tomdaytwo@xxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 14 May 2014 12:36:48 -0400
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)
));
Other related posts:
- » Using two pivots while creating a view - Thomas Day
