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

    Question 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
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    How do you define what belongs to a single row in the pivoted table?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    May 2012
    Posts
    5
    Quote Originally Posted by stolze View Post
    How do you define what belongs to a single row in the pivoted table?
    I mean Username will be a column and all values of username will come under the same, suppose for username no value of class is assigned then it shld be updated null.

  4. #4
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Which database is being used?
    Which columns are expected in the result?

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by sandipjee View Post
    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
    (1) Use [code] tag for readability. Multiple blanks in your sample input data were displayed as one blank for me.

    (2) There is no implicit/assumed sequence between rows(records) in a table in Relational Database.
    So, sequence(or number) column(s) should be added, like
    Code:
    id  LHS        RHS
    --- ---------- ---------------
      1 User-Name  765237538
      3 Class      kjasyd78632
      5 SSSID      68768qw
      6 Part       yts56iu
      7 User-Name  8762398269
      8 Checker    7639369ttt
     12 Part       72973
    or
    Code:
    recno seq LHS        RHS
    ----- --- ---------- ---------------
        1   1 User-Name  765237538
        1   3 Class      kjasyd78632
        1   5 SSSID      68768qw
        1   6 Part       yts56iu
        2   1 User-Name  8762398269
        2   2 Checker    7639369ttt
        2   6 Part       72973
    (3) State more concretely your expected results.

    (3-1) For example, show your expected results like this.
    Although this might be different from what you wanted,
    this is just an example how to show your required result from your sample input data.
    Code:
    User-Name  Checker      Class        SSSID    Part
    ---------- ------------ ------------ -------- ----------
    765237538               kjasyd78632  68768qw  yts56iu
    8762398269 7639369ttt                         72973
    (3-2) How many columns are you expected.
    (3-2a) Do you know all possible LHS values?
    (3-2b) If you don't know all LHS values and other data(later than shown example) had other LHS values,
    do you want to add the columns last to exisitng columns?
    However, this might be not possible in SQL,
    without using dynamic SQL to read all LHS values and construct a SELECT statement before read RHS values and generate final result.
    Last edited by tonkuma; 05-12-12 at 06:04.

  6. #6
    Join Date
    Oct 2012
    Posts
    2

    Cool using sql converting rows into columns into rows

    Select max(decode(rw,1,str,null)) "fir",
    max(decode(rw,2,str,null)) "sec",
    max(decode(rw,3,str,null)) "thi",
    max(decode(rw,4,str,null)) "fou",
    max(decode(rw,5,str,null)) "fif",
    max(decode(rw,6,str,null)) "six",
    max(decode(rw,7,str,null)) "sev",
    max(decode(rw,8,str,null)) "eig",
    max(decode(rw,9,str,null)) "nin",
    max(decode(rw,10,str,null)) "ten",
    max(decode(rw,11,str,null)) "ele",
    max(decode(rw,12,str,null)) "twe",
    max(decode(rw,13,str,null)) "thr",
    max(decode(rw,14,str,null)) "frt"
    from
    (
    select empno||chr(10)||ename||chr(10)||job||chr(10)||sal| |chr(10)||comm||chr(10)||hiredate||chr(10)||deptno str,row_number() over(order by empno) rw from emp
    )
    Last edited by proddaturmastan; 10-21-12 at 15:18.

  7. #7
    Join Date
    Oct 2012
    Posts
    2

    solution for converting rows into columns

    Select max(decode(rw,1,str,null)) "fir",
    max(decode(rw,2,str,null)) "sec",
    max(decode(rw,3,str,null)) "thi",
    max(decode(rw,4,str,null)) "fou",
    max(decode(rw,5,str,null)) "fif",
    max(decode(rw,6,str,null)) "six",
    max(decode(rw,7,str,null)) "sev",
    max(decode(rw,8,str,null)) "eig",
    max(decode(rw,9,str,null)) "nin",
    max(decode(rw,10,str,null)) "ten",
    max(decode(rw,11,str,null)) "ele",
    max(decode(rw,12,str,null)) "twe",
    max(decode(rw,13,str,null)) "thr",
    max(decode(rw,14,str,null)) "frt"
    from
    (
    select empno||chr(10)||ename||chr(10)||job||chr(10)||sal| |chr(10)||comm||chr(10)||hiredate||chr(10)||deptno str,row_number() over(order by empno) rw from emp
    )

Posting Permissions

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