Forum Stats

  • 3,851,640 Users
  • 2,264,007 Discussions
  • 7,904,801 Comments

Discussions

Pivot column into row

848002
848002 Member Posts: 23
edited Jul 16, 2014 1:52PM in SQL & PL/SQL

Hello All,

Having an hard time trying to make a pivot in SQL.

E.G. :

labelvalue_textvalue_dateid
Po #345NULL112
Po #123456NULL120
Po #935131NULL134

So I would like to have the label section as a column name and put the value_text under the appropriate column but want to keep the ID in order to make a table with my view and join it to another table on a sql request.

Is that possible ?

Regards,

Eric

Tagged:

Answers

  • L-MachineGun
    L-MachineGun Member Posts: 926 Silver Badge
    848002 wrote:
    
    Hello All,
    
    . . .   E t c   . . .
    Is that possible ? Regards, Eric

    Yes, but you would only have one column "Po #"

    L-MachineGun
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,475 Red Diamond
    edited Jul 16, 2014 1:07PM

    Hi, Eric,

    Sorry, it's not clear what you want.

    Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.
    Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
    Always say which version of Oracle you're using (for example, 11.2.0.2.0).
    See the forum FAQ: https://forums.oracle.com/message/9362002#9362002

    The compiler needs to know any column names or aliases that are used in a query.  Only after the statement is compiled does it start to fetch data.  Therefore, you can't have column names or aliases that depend on the data fetched.

    If you want to have dynamic column names, then you need dynamic SQL.  Your front end may have tools that make dynamic SQL easier.  In SQL*Plus, for example, you can use substitutiuon variables.

    Frank Kulash
  • 848002
    848002 Member Posts: 23

    Thanks,

    As I've already transform my view into a table , here are my current statement :

    SELECT * FROM table.df;

    Then here is my result :

    labelvalue_textvalue_dateid
    P/O line#345NULL1
    P/O line#50NULL2
    P/O line#10NULL3
    P/O line#10NULL1
    P/O line#10NULL2
    P/O line#10NULL3
    P/O line#30NULL1
    P/O line#10NULL2
    P/O line#20NULL3
    P/O line#10NULL1
    P/O line#10NULL3
    Po #345NULL3
    Po #123456NULL2
    Po #935131NULL4
    Po #931703NULL5
    Po #12345NULL6
    Po #935773NULL7

    And this is what I would like to have :

    Po #P/O line#value_dateid
    345345NULL1
    12345650NULL2
    93513110NULL3
    93170310NULL1
    1234510NULL2
    93577310NULL3
    50541430NULL1
    50488410NULL2
    50488420NULL3
    93432210NULL1

    Hope that is more clear,

    Eric

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,475 Red Diamond
    edited Jul 16, 2014 1:54PM

    Hi, Eric,

    That sure does look like a pivot.  "MIN (CASE ...) ... GROUP BY" might be simpler than "SELECT ... PIVOT" in this case.  A self-join is another possibility.

    I can't figure out what each row of the output represents.  For example, why do you want

    Po #    P/O line#  value_date  id
    ------  ---------  ----------  --
    123456  50         NULL        2
     

    in the result set, and not

    Po #    P/O line#  value_date  id
    ------  ---------  ----------  --
    123456  10         NULL        2
     

    or would you be equally satisfied with either one?  Once again, post CREATE TABLE and INSERT statements to reproduce the data (no matter where the data actually comes from) and explain how you get the desired results from the given data.

    Remember, there is no built-in order to rows in a relational database, so if you use words like "first" or "next" in your explanation, be sure to say what it is in the data that makes a row first or next.

This discussion has been closed.