Results 1 to 9 of 9
  1. #1
    Join Date
    May 2012
    Posts
    5

    Exclamation Unanswered: Need help in converting rows into columns

    Hi Gurus

    Need help converting rows to columns via SQL or Pl/Sql

    Input table looks like this:

    LHS RHS
    ---- -----
    User-Name 765237538
    Class kjasyd78632
    SSSID 68768qw
    Part yts56iu
    User-Name 8762398269
    Checker 7639369ttt
    Part 72973

    and so on

    The values at LHS are not same for all records but all records starts with User-Name

    I need to generate out put such as the names in LHS come as columns with respective values at RHS, if the field is not present at record the same shld be null.

    Please help

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2012
    Posts
    5
    Quote Originally Posted by anacedent View Post
    I have already checked that but the issue is I have only one table and not really with any key value. Not able to pivot the same.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    I don't understand, You state:
    The values at LHS are not same for all records but all records starts with User-Name

    Since 'LHS' is your first column AND all your rows start with "User-Name", so your table has two columns(LHS and RHS).
    Do your rows look like this:

    Code:
    LHS        RHS
    ----       ------------------------------------------------------
    User-Name  765237538 Class kjasyd78632 SSSID 68768qw Part yts56iu
    User-Name  8762398269 Checker 7639369ttt Part 72973

    Or are you reading a flat file in via utl_file?

  6. #6
    Join Date
    May 2012
    Posts
    5
    Quote Originally Posted by spacebar View Post
    I don't understand, You state:

    Since 'LHS' is your first column AND all your rows start with "User-Name", so your table has two columns(LHS and RHS).
    Do your rows look like this:

    Code:
    LHS        RHS
    ----       ------------------------------------------------------
    User-Name  765237538 Class kjasyd78632 SSSID 68768qw Part yts56iu
    User-Name  8762398269 Checker 7639369ttt Part 72973

    Or are you reading a flat file in via utl_file?
    Nope, my table looks exactly like I posted....

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by sandipjee View Post
    I have already checked that but the issue is I have only one table and not really with any key value. Not able to pivot the same. :confused:
    So, in other words, you are unable to detect which rows in current table make one row in expected result set?
    If so, you are out of luck - there is no "default" order in relational table.
    From 11gR2 Concepts book: http://docs.oracle.com/cd/E11882_01/...cls.htm#i20438
    By default, a table is organized as a heap, which means that the database places rows where they fit best rather than in a user-specified order. Thus, a heap-organized table is an unordered collection of rows. As users add rows, the database places the rows in the first available free space in the data segment. Rows are not guaranteed to be retrieved in the order in which they were inserted.
    Also take INSERTs into account - they may put the row anywhere in the table - far away from other "respective" rows. So, are you able to determine which rows fit together just by looking at unordered data? How? And no, the output of an unordered query is not the correct method - it may (and will) differ.

  8. #8
    Join Date
    Feb 2005
    Posts
    57
    Show us the actual table definition of your 'input table'.

    What do you get when you 'SELECT * FROM <input_table>' ?

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Is the table an external flat file or an oracle table. If it's oracle, you are totally out of luck.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •