CODEWITHSUNDEEP
sqloracle-database
John Wick
John Wick

Reputation: 745

How to pivot on two types of aggregations of the same column in Oracle?

So I am currently pivoting on a column called 'RESP', but the kicker here is that I need to use both the 'MAX' and 'SUM' aggregate function depending on the ID. How can I go about doing this? I've tried using a CASE statement in my PIVOT statement but was unsuccessful.

Some Sample Data is below:

Value        Value_Type         Date            Gender      Age    Name
 83          Income             01-01-19        Male        19     John
 95          Income             01-01-19        Male        19     John
 Civic       Model              01-01-19        Male        19     John
 Blue        Color              01-01-19        Male        19     John

I need to pivot so I can receive the following data set:

   Date       Gender     Age      Name       Income     Car     Color
  01-01-19    Male       19       John       178        Civic   Blue

Below is what I tried:

SELECT * FROM (
                SELECT value
                     , date
                     , age
                     , name
                     , gender
                  FROM Table
                 PIVOT (
                        CASE WHEN value_Type IN ('Model', 'Color')
                             THEN max(value)
                             WHEN value_type IN ('Income')
                             THEN sum(value)
                              END
                        )
               );

Is there any way to use the PIVOT statement to get this? Or will I need to use a conditional MAX(CASE WHEN) and then group/aggregate to get my desired data set?

Thanks in advance.

Upvotes: 0

Views: 77

Answers (3)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

It is possible with pivot:

select date_, gender, age, name, inc_sm income, mod_mx, col_mx
  from tbl
  pivot (max(value) mx, sum(case value_type when 'Income' then value end) sm
         for value_type in ('Model' mod, 'Color' col, 'Income' inc))

dbfiddle

Upvotes: 1

Popeye
Popeye

Reputation: 35910

You can achieve this using the following query:

With DATAA (VALUE, VALUE_TYPE, DATE1, GENDER, AGE, NAME)
AS
(
SELECT '83', 'Income', date '2019-01-01', 'Male', 19, 'John' from dual union all
SELECT '95', 'Income', date '2019-01-01', 'Male', 19, 'John' from dual union all
SELECT 'Civic', 'Model', date '2019-01-01', 'Male', 19, 'John' from dual union all
SELECT 'Blue', 'Color', date '2019-01-01', 'Male', 19, 'John' from dual
)
SELECT
    DATE1,
    GENDER,
    AGE,
    NAME,
    I_INCOME1   AS INCOME,
    M_MODEL1    AS CAR,
    C_COLOR1    AS COLOR
FROM
    (
        SELECT
            VALUE,
            DATE1,
            AGE,
            NAME,
            GENDER,
            VALUE_TYPE
        FROM
            DATAA
    ) PIVOT (
        SUM ( CASE
            WHEN VALUE_TYPE = 'Income' THEN VALUE
        END )
    AS INCOME1, MAX ( CASE
        WHEN VALUE_TYPE = 'Model' THEN VALUE
    END ) AS MODEL1, MAX ( CASE
        WHEN VALUE_TYPE = 'Color' THEN VALUE
    END ) AS COLOR1
        FOR VALUE_TYPE
        IN ( 'Income' AS I, 'Model' AS M, 'Color' AS C )
    );

Output:

DATE1               GEND        AGE NAME     INCOME CAR   COLOR
------------------- ---- ---------- ---- ---------- ----- -----
01-01-2019 12:00:00 Male         19 John        178 Civic Blue

Cheers!!

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269933

This looks like conditional aggregation to me:

select Date, Gender, Age, Name,
       sum(case when value_type = 'Income' then cast(value as number) end) as income,
       max(case when value_type = 'Model' then value end) as model,
       max(case when value_type = 'Color' then value end) as color
from t
group by Date, Gender, Age, Name;

Upvotes: 2

Related Questions