0

I have a table(EMP_Comm_Calc) as shown below:

Min-Wage    Max-Wage    Comm%   Work_Type_ID    Working_Days    Max_Allowed Min_Allowed
400 2000    30  2426    20  2000    1888
450 2000    30  2426    30  2000    1888
1680    2000    54  2426    20  2000    1888
1680    2000    54  2426    30  2000    1888
1900    2000    65  2426    20  2000    1888
1950    2000    65  2426    30  2000    1888
450 2500    30  2427    20  2500    1999
450 2500    30  2427    30  2500    1999
2100    2500    54  2427    20  2500    1999
2100    2500    54  2427    30  2500    1999
2380    2500    65  2427    20  2500    1999
2380    2500    65  2427    30  2500    1999

Now I want to transpose the rows based on Comm% and Working_days for each Work_type_id, the expected output is:

Work_Type_ID    Min2065 Max2065 Min2030 Max2030 Min2054 Max2054 Min3065 Max3065 Min3030 Max3030 Min3054 Max3054 Max_allowed Min_Allowed
2426    1900    2000    400 2000    1680    2000    1950    2000    450 2000    1680    2000    2000    1888
2427    2380    2500    450 2500    2100    2500    2380    2500    450 2500    2100    2500    2500    1999
2
  • Possible duplicate of Oracle Pivot - converting values into columns Commented Jun 7, 2016 at 10:16
  • Welcome to SO! There are literally thousands of similar questions on SO - just search for Oracle pivot. Voted for closing. Commented Jun 7, 2016 at 10:17

1 Answer 1

0

Oracle Setup:

CREATE TABLE table_name ( Min_Wage, Max_Wage, "Comm%", Work_Type_ID, Working_Days, Max_Allowed, Min_Allowed ) AS
SELECT 400, 2000, 30, 2426, 20, 2000, 1888 FROM DUAL UNION ALL
SELECT 450, 2000, 30, 2426, 30, 2000, 1888 FROM DUAL UNION ALL
SELECT 1680, 2000, 54, 2426, 20, 2000, 1888 FROM DUAL UNION ALL
SELECT 1680, 2000, 54, 2426, 30, 2000, 1888 FROM DUAL UNION ALL
SELECT 1900, 2000, 65, 2426, 20, 2000, 1888 FROM DUAL UNION ALL
SELECT 1950, 2000, 65, 2426, 30, 2000, 1888 FROM DUAL UNION ALL
SELECT 450, 2500, 30, 2427, 20, 2500, 1999 FROM DUAL UNION ALL
SELECT 450, 2500, 30, 2427, 30, 2500, 1999 FROM DUAL UNION ALL
SELECT 2100, 2500, 54, 2427, 20, 2500, 1999 FROM DUAL UNION ALL
SELECT 2100, 2500, 54, 2427, 30, 2500, 1999 FROM DUAL UNION ALL
SELECT 2380, 2500, 65, 2427, 20, 2500, 1999 FROM DUAL UNION ALL
SELECT 2380, 2500, 65, 2427, 30, 2500, 1999 FROM DUAL;

Query:

SELECT *
FROM   (
  SELECT Work_Type_ID,
         Working_Days||"Comm%" AS pivotvalue,
         Min_Wage,
         Max_Wage,
         Max_Allowed,
         Min_Allowed
  FROM   table_name
)
PIVOT (
  MIN( Min_wage ) AS Min,
  MAX( Max_Wage ) AS Max
  FOR pivotvalue IN ( 2065, 2030, 2054, 3065, 3030, 3054 )
);

Output:

WORK_TYPE_ID MAX_ALLOWED MIN_ALLOWED   2065_MIN   2065_MAX   2030_MIN   2030_MAX   2054_MIN   2054_MAX   3065_MIN   3065_MAX   3030_MIN   3030_MAX   3054_MIN   3054_MAX
------------ ----------- ----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
        2426        2000        1888       1900       2000        400       2000       1680       2000       1950       2000        450       2000       1680       2000 
        2427        2500        1999       2380       2500        450       2500       2100       2500       2380       2500        450       2500       2100       2500 
Sign up to request clarification or add additional context in comments.

1 Comment

@SGW : How have you written the pivotvalue portion ?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.