Rotate/Pivot Table with Aggregation in Oracle

Rotate/pivot table with aggregation in Oracle

Yes I think so. It is easy to do a pivot like this with a MAX aggregate:

SELECT
    *
FROM
(
    SELECT
        project,
        attribute,
        value
    FROM
        table1
) AS SourceTable
PIVOT
(
    MAX(value)
    FOR attribute IN ([foo],[bar],[baz])
) AS pvt

Otherwise you have to do a case statement inside the a max aggregate. Like this:

SELECT
    MAX(CASE WHEN attribute='foo' THEN value ELSE NULL END) AS foo,
    MAX(CASE WHEN attribute='bar' THEN value ELSE NULL END) AS bar,
    MAX(CASE WHEN attribute='baz' THEN value ELSE NULL END) AS baz,
    project
FROM
    table1
GROUP BY
    project

This is almost the same thing as doing the PIVOT. But I would prefer doing the PIVOT over the CASE WHEN MAX..

Transpose rows into columns using Pivot in Oracle

With pivot you could do:

select *
from your_table
pivot (
  max(coalesce(text, to_char(num)))
  for (fieldname) in ('Width' as width, 'Height' as height, 'Comments' as comments))

ID   WIDTH HEIGHT COMMENTS
---- ----- ------ ---------
1051 121   2      FP-SK/124
1170 5678  5

I've used max(coalesce(text, to_char(num))) because you have two columns to cram into one, effectively, and you need to_char() because they are different data types. If a row could have a value in both columns then the value you end up with might not be what you want, but then you'd need to define what should happen in that case.

You could also use conditional aggregation, which is what pivot does under the hood anyway; here simplified to not coalesce, on the assumption you won't have both columns populated:

select id,
  max(case when fieldname = 'Width' then text end) as width,
  max(case when fieldname = 'Height' then num end) as height,
  max(case when fieldname = 'Comments' then text end) as comments
from your_table
group by id

ID   WIDTH HEIGHT COMMENTS
---- ----- ------ ---------
1051 121        2 FP-SK/124
1170 5678       5

db<>fiddle

Notice that the height value is now a number; in the pivot version it is - and must be - a string. You can convert the result to a different data type of course.

How do we get multiple values in the output separated by commas

You can change from max() to listagg():

select *
from your_table
pivot (
  listagg(coalesce(text, to_char(num)), ',')
  for (fieldname) in ('Width' as width, 'Height' as height, 'Comments' as comments))

or

select id,
  listagg(case when fieldname = 'Width' then text end, ',') within group (order by text) as width,
  listagg(case when fieldname = 'Height' then num end, ',') within group (order by text) as height,
  listagg(case when fieldname = 'Comments' then text end, ',') within group (order by text) as comments
from your_table
group by id

which both get

  ID WIDTH      HEIGHT     COMMENTS
---- ---------- ---------- ----------
1051 121,95                Sample
1170 5678       2,5

db<>fiddle

Oracle SQL query with Pivot and aggregate function

If I understand what you're trying to do, I'd move the decision on which column to use up into the main query, with a case expression inside a single sum():

SELECT unique state_code, city_code, 
  equipment_type, 
  sum(case when equipment_type in ('Gabion Basket',
      'Rapid Deployment Floodwall', 'Portable Coffer Dam')
    then AVAILABLE_COUNT_LENGTH
    else AVAILABLE_COUNT end) AS chosen_count
FROM EQUIP_VIEW_TABLE
GROUP BY state_code, city_code, equipment_type

and leave the pivot as it was:

SELECT * 
FROM ( SELECT unique state_code, city_code, 
     equipment_type, 
     sum(case when equipment_type in ('Gabion Basket',
         'Rapid Deployment Floodwall', 'Portable Coffer Dam')
       then AVAILABLE_COUNT_LENGTH
       else AVAILABLE_COUNT end) AS chosen_count
     FROM EQUIP_VIEW_TABLE
     GROUP BY state_code, city_code, equipment_type )
pivot (max(chosen_count)
     for (equipment_type) in 
     ('Sandbag' "CNT_SANDBAG",
      'Gabion Basket' "CNT_GABION_BASKET",
      'Rapid Deployment Floodwall' "CNT_RAPID_DEPLOYMENT_FLOODWALL",
      'Portable Coffer Dam' "CNT_PORTABLE_COFFER_DAM",
      'Polyethylene Sheeting' "CNT_POLYETHYLENE_SHEETING",
      'Pump' "CNT_PUMP"))

Select latest and earliest times within a time group and a pivot statement

First, you need to normalize your data by removing the duplicate entries. In your situation, that's a challenge because the duplicated data isn't easily identified as a duplicate. You can make some assumptions though. Below, I assume that no one will make multiple login attempts in a two minute window. You can do this by first using a Common Table Expression (CTE, using the WITH clause).

Within the CTE, you can use the LAG function. Essentially what this code is saying is "for each partition of user and entry type, if the previous value was within 2 minutes of this value, then put a number, otherwise put null." I chose null as the flag that will keep the value because LAG of the first entry is going to be null. So, your CTE will just return a table of entry events (ID) that were distinct attempts.

Now, you prepare another CTE that a PIVOT will pull from that has everything from your table, but only for the entry IDs you cared about. The PIVOT is going to look over the MIN/MAX of your IN/OUT times.

WITH UNIQUE_LOGINS AS (
    SELECT ID FROM LOGIN_TABLE 
    WHERE CASE WHEN LAG(TIME, 1, 0) OVER (PARTITION BY USERNAME, STATUS ORDER BY TIME) 
              + (2/60/24) < TIME THEN NULL ELSE 1 END IS NULL ),   -- Times within 2 minutes
TEMP_FOR_PIVOT AS (
    SELECT USERNAME, TIME, STATUS FROM LOGIN_TABLE WHERE ID IN (SELECT ID FROM UNIQUE_LOGINS)
)
SELECT * FROM TEMP_FOR_PIVOT
PIVOT (
    MIN(TIME), MAX(TIME) FOR STATUS IN ('IN', 'OUT')
)

From there, if you need to rearrange or rename your columns, then you can just put that last SELECT into yet another CTE and then select your values from it. There is some more about PIVOT here: Rotate/pivot table with aggregation in Oracle

Pivot using two aggregate columns

You can't pivot twice on the same step, since doing a pivot does an implicit GROUP BY of the non referenced columns. You will have to apply the pivot only on a subset of columns that don't have distinct values aside from the aggregate function and the pivoted column. Try the following:

;WITH PrePivot1 AS
(
    SELECT
        T.col1,
        T.per1
    FROM
        YourTable AS T
),
PrePivot2 AS
(
    SELECT
        T.col1,
        T.per2
    FROM
        YourTable AS T
),
Pivot1 AS
(
    SELECT
        P.A,
        P.B,
        P.C
    FROM
        PrePivot1 AS R
        PIVOT (
            MAX(R.per1) FOR R.Col1 IN ([A], [B], [C])
        ) AS P
),
Pivot2 AS
(
    SELECT
        P.A,
        P.B,
        P.C
    FROM
        PrePivot2 AS R
        PIVOT (
            MAX(R.per2) FOR R.Col1 IN ([A], [B], [C])
        ) AS P
)
SELECT
    Concept = 'Per1',
    P1.A,
    P1.B,
    P1.C,
FROM
    Pivot1 AS P1
UNION ALL
SELECT
    Concept = 'Per2',
    P2.A,
    P2.B,
    P2.C,
FROM
    Pivot2 AS P2

Edit:

To do further calculations, you can keep chaining CTEs. Change

SELECT
    Concept = 'Per1',
    P1.A,
    P1.B,
    P1.C,
FROM
    Pivot1 AS P1
UNION ALL
SELECT
    Concept = 'Per2',
    P2.A,
    P2.B,
    P2.C,
FROM
    Pivot2 AS P2

with

,
UnionResults AS
(
    SELECT
        Concept = 'Per1',
        P1.A,
        P1.B,
        P1.C,
    FROM
        Pivot1 AS P1
    UNION ALL
    SELECT
        Concept = 'Per2',
        P2.A,
        P2.B,
        P2.C,
    FROM
        Pivot2 AS P2
)
SELECT
    U.Concept,
    U.A,
    U.B.
    U.C,
    ADivision = CASE
        WHEN U.C <> 0
        THEN U.A / U.C END
FROM
    UnionResults AS U

Dynamic Pivot in Oracle's SQL

You can't put a non constant string in the IN clause of the pivot clause.

You can use Pivot XML for that.

From documentation:

subquery A subquery is used only in conjunction with the XML keyword.
When you specify a subquery, all values found by the subquery are used
for pivoting

It should look like this:

select xmlserialize(content t.B_XML) from t_aa
pivot xml(
sum(A) for B in(any)
) t;

You can also have a subquery instead of the ANY keyword:

select xmlserialize(content t.B_XML) from t_aa
pivot xml(
sum(A) for B in (select cl from t_bb)
) t;

Here is a sqlfiddle demo

---

Related Topics