Pivot Tables

30303b71-e15b-44a4-a3f9-6a4680ce976f

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

mNem

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

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.