Forum Stats

  • 3,852,528 Users
  • 2,264,112 Discussions
  • 7,905,094 Comments

Discussions

Pivot Tables

30303b71-e15b-44a4-a3f9-6a4680ce976f
edited Dec 24, 2018 12:08PM in SQL & PL/SQL

Hi guys,

I'm new to Oracle and I'm having a bit of trouble with the PIVOT command, most of the examples I've seen seem to revolve around counts and sums in the pivoyed column. I want to see the actual values in each column.

For example, I have a table that looks like this

EmployeeId    CategoryId    Value

1        1        UK

1        2        12345

1        3        XYZ

2        1        IE

2        2        56789

3        1        US

3        2        98765

3        3        ABC

And, what I want to see id this

EmployeeId    1    2    3

1        UK    12345    XYZ

2        IE    56789

3        US    98765    ABC

Any help would be appreciated

Tagged:

Answers

  • mNem
    mNem Member Posts: 1,380 Gold Trophy
    edited Dec 24, 2018 4:30AM

    with t (EmployeeId,CategoryId,Value) as

    (

    select 1,        1,        'UK'    from dual union all

    select 1,        2,        '12345' from dual union all

    select 1,        3,        'XYZ'   from dual union all

    select 2,        1,        'IE'    from dual union all

    select 2,        2,        '56789' from dual union all

    select 3,        1,        'US'    from dual union all

    select 3,        2,        '98765' from dual union all

    select 3,        3,        'ABC'   from dual

    )

    select * from t

    pivot

    (

    min(value) for (categoryid) in (1, 2, 3)

    )

    ;

    EMPLOYEEID 1     2     3  

    ---------- ----- ----- -----

             1 UK    12345 XYZ  

             2 IE    56789      

             3 US    98765 ABC

    Here is another way not using PIVOT.

    select employeeid
    , min(decode(categoryid, 1, value)) as "1"
    , min(decode(categoryid, 2, value)) as "2"
    , min(decode(categoryid, 3, value)) as "3"
    from t
    group by employeeid
    order by 1
    ;

  • mathguy
    mathguy Member Posts: 10,675 Blue Diamond
    edited Dec 24, 2018 12:08PM

    You are right: in most cases (I shouldn't say that, since it's not true, but I will still say it: "in most cases") pivoting is used in tandem with an aggregate function like COUNT() or SUM().

    This is because PIVOT is, indeed, and aggregation operator. Just like with COUNT() and SUM(), the inputs have many rows with the same employee id, but the output has just one aggregated row for each employee id. If you don't use the PIVOT operator, but you "pivot" the data the old way, the way it used to be done before Oracle version 11.1 (which introduced the PIVOT operator) - the way mNem demonstrated in the second part in Reply 1 - that becomes even more evident.

    If you simply "pivot" existing data without needing any count, sum or anything else of the sort, you still must use an aggregate function - in most cases MIN or MAX.

    Be advised, though, that a common mistake we keep seeing is queries where a "standard" aggregation is done, with grouping by both employee id and by category id (using your example), and then, after that's done, people want to use PIVOT to get the data in pivoted format. Then they would use a MIN or MAX as above. That shows pretty bad understanding of PIVOT; instead, the aggregation (by SUM, COUNT, or whatever - even MIN, MAX, ...) should be done within the PIVOT operator; both operations, "aggregation within a single column" and then "pivoting for display purposes" should be done in a SINGLE PASS over the data, using PIVOT.

    Be advised, also, that sometimes (when the needed aggregations are more complicated) the PIVOT operator is not the best approach. One could do the complicated aggregation in one pass, and then apply PIVOT to the result just to change the displaying of the data; but everything CAN be done in a single pass, if one uses THE OLD WAY of aggregation - the conditional aggregation mNem demonstrated - which is much more flexible than the PIVOT operator.

    Don't worry if this doesn't make a lot of sense to you yet (since you haven't seen any examples to illustrate what I am talking about) - just try to remember them later, when you do run into such issues.