PL/SQL (MOSC)

MOSC Banner

How do I use a PIVOT query with a cursor in PL/SQL?

edited Mar 13, 2012 9:59PM in PL/SQL (MOSC) 4 commentsAnswered
In my simple example I have a table with the columns user_name and Is_admin.The Is_admin column only has two distinct values: 0 and 1.

With this SQL statement I can create a Pivot table:

    With Pivot_data as (select  User_name, Is_admin admin from Users)
    select * from Pivot_data
    PIVOT  (sum(admin)  for admin in (0 AS xUser,1 as xAdmin ));

Result
    USER_NAME    XUSER    XADMIN
    User1                  0
    User2                  0
    User3                                  1

I need to assign the results to a cursor variable in a PL/SQL procedure.
(like  "cursor usr is" and   "open usr_cur for"
However, I can't find a way to do this.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center