Forum Stats

  • 3,853,209 Users
  • 2,264,192 Discussions
  • 7,905,287 Comments

Discussions

Pivot Without Aggregation

3234063
3234063 Member Posts: 12
edited May 30, 2016 11:38AM in SQL & PL/SQL

Oracle 11g Express

I need to know how to pivot a result set without using aggregation.

For example I have the following:

Select A, B, C From Table_Name where A < 9

A     B     C

--     --     --

1     x     z

4     v     i

2     u     y

I want to get back

X     Y     Z

--     --     --

1     4     2

x     v     u

z     i     y

Please note that in my actual query I will have approximately 180 fields to pivot and my final result will have a variable number of columns. Some of the data types are numeric and some are varchar.

Thank you,

Scott

Tagged:

Answers

  • mathguy
    mathguy Member Posts: 10,675 Blue Diamond
    edited May 30, 2016 8:48AM

    This doesn't make sense in several ways. Let's start with the most obvious. In your output you want to have columns X, Y, Z. Column X will contain the values 1 (a number), 'x' and 'z' (string values). There is no such thing in Oracle. A column can only have values of the same datatype.

    If this is for display purposes only, and not for use in any further database processing, you can cast the numbers to string (and apply formatting masks, etc.) Is that what you need? In any case, such manipulations are much better done in your application, not in the database (if you are not using the result for further database operations).

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,728 Red Diamond
    edited May 30, 2016 8:56AM
    3234063 wrote:
    
    I need to know how to pivot a result set without using aggregation.
    

    You can't statically pivot this even with aggregation since number of rows returned by Select A, B, C From Table_Name where A < 9 is unknown. And any dynamic pivot makes sense in reporting only. And reporting tools have all pivoting bells and whistles by design. You can do it in SQL*Plus using substitution variables or PL/SQL + refcursor bind variable, but why?


    SY.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,492 Red Diamond
    edited May 30, 2016 9:02AM

    Hi,

    Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the exact results you want from that data, so that the people who want to help you can re-create the problem and test their ideas.

    Explain, using specific examples, how you get those results from that data.

    Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

    See the forum FAQ: 

    The number of columns in a query must be hard-coded at compile time.  If you have a variable number of columns, then you need dynamic SQL.

    You can unpivot the data into a single column, and then pivot it back into the form you want it.  See How to transpose the table?

    Instead of having a variable number of output columns, you can use String Aggregation to put all the values for a row into one giant string, formatted so that it looks like separate columns.  (You can do this without dynamic SQL.)

  • Vysakh Suresh - 3035408
    Vysakh Suresh - 3035408 Member Posts: 1,240 Gold Badge
    edited May 30, 2016 9:26AM

    Dear 3234063,

    Kindly keep in mind 2 points moving forward with PIVOT clauses.. this will help you in future..

         1) The PIVOT operator takes data in separate rows, aggregates it and converts it into columns. So avoiding the aggregate function is  practically impossible..


         2) You cannot have Dynamic values within the select list (what I meant here is that a subquery will not be possible inside) of the PIVOT clause.. So with the varying number of columns you will not be able to use a subquery to suit the requirement.. the value in select list., which will eventually end up as the columns in the result should be provided manually.


    Thanks and Regards,

    Vysakh Suresh

  • Vysakh Suresh - 3035408
    Vysakh Suresh - 3035408 Member Posts: 1,240 Gold Badge
    edited May 30, 2016 9:29AM
  • 3234063
    3234063 Member Posts: 12
    edited May 30, 2016 9:30AM

    The desired result will not be processed further. It would be inserted into excel for display. The best way I have thought of so far to have Excel reformat the data is to copy a standard query result into Excel and then copy/past with transpose. I think that will work but I was trying to avoid it.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,728 Red Diamond
    edited May 30, 2016 9:51AM

    And how result will be fed to Excel? Are you planning to spool results as CSV, use UTL_FILE, etc?

    SY.

  • Sven W.
    Sven W. Member Posts: 10,551 Gold Crown
    edited May 30, 2016 11:38AM
    3234063 wrote:
    
    The desired result will not be processed further. It would be inserted into excel for display. The best way I have thought of so far to have Excel reformat the data is to copy a standard query result into Excel and then copy/past with transpose. I think that will work but I was trying to avoid it.
    

    You can put the data in excel on one page and use an excel PIVOT operation on the other page.

    Transpose will work too, but shouldn't be needed.

    All the "issues" we have in SQL will excel have as well. There you also will need to name an aggregation function and so on.

    If the Excel pivot function is not able to solve the issue, then the SQL pivot function is also unlikely to be helpful.

This discussion has been closed.