Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56

    Arrow Unanswered: Oracle sql - row to column

    I have the following data

    empid jobid jobcode
    1000 2000 A2
    1000 3000 A3
    1000 4000 A4
    1001 2000 A2
    1001 4000 A4
    1002 3000 A3
    1002 4000 A4
    1002 5000 A5
    ...

    p.s. jobid only has 5 values: 1000, 2000, 3000, 4000 and 5000


    I want to get the following result:

    empid jobid_1000 jobid_2000 jobid_3000 jobid_4000 jobid_5000
    1000 A2 A3 A4
    1001 A2 A4
    1002 A4 A5
    ...

    (A1 under jobid_1000 column, A2 under jobid_2000 column, A3 under jobid_3000 column, A4 under jobid_4000 column and A5 under jobid_5000 column)

    Any idea? Please kindly offer help.
    Last edited by shev; 10-17-03 at 06:10.
    Cheers,
    Shev

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    you want to use decode

    this will invert everything for you

    It is probably easier for you to look this up on asktom or the online docs
    than it would be for me to explain it.

    short example:

    PHP Code:
    select
    emp_id
    ,
    MAXDECODEjobid'1000'jobcodeNULL ) ) "job1000",
    MAXDECODEjobid'2000'jobcodeNULL ) ) "job2000",
    MAXDECODEjobid'3000'jobcodeNULL ) ) "job3000",
    MAXDECODEjobid'4000'jobcodeNULL ) ) "job4000",
    MAXDECODEjobid'5000'jobcodeNULL ) ) "job5000",
    from
    (select empidjobidjobcode
    from table_name

    i think that is correct.
    someone else please help if that is not right.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56
    Hi The_Duck,

    I have tried your query. The result is below

    empid jobid_1000 jobid_2000 jobid_3000 jobid_4000 jobid_5000
    1000 A2
    1000 A3
    1000 A4
    1001 A2
    1001 A4
    1002 A4
    1002 A5

    This is not exactly what I want but seems very near. I really get stuck on it. Do you have any idea? Anyone has other suggestions?

    Thanks in advance.
    Cheers,
    Shev

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    you mean to tell me I gotta write the whole thing for you??

    go here and lookup decode dood:
    http://tahiti.oracle.com/pls/db92/db92.homepage
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    bah!
    I re-wrote it to work. I was missing the group_by clause
    Enjoy.
    (just change emp_id to empid and add your correct table-name)

    PHP Code:
    select
    emp_id
    ,
    MAXDECODEjobid'1000'jobcodeNULL ) ) "job1000",
    MAXDECODEjobid'2000'jobcodeNULL ) ) "job2000",
    MAXDECODEjobid'3000'jobcodeNULL ) ) "job3000",
    MAXDECODEjobid'4000'jobcodeNULL ) ) "job4000",
    MAXDECODEjobid'5000'jobcodeNULL ) ) "job5000"
    from
    (select emp_idjobidjobcode
    from test
    )
    group by emp_id
    output:
    PHP Code:
     14:50:33 kod:stagedemo> /

    EMP_ID     job1000    job2000    job3000    job4000    job5000
    ---------- ---------- ---------- ---------- ---------- ----------
    1000                  A2
    1001                                        A4 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56
    Thanks The_Duck.

    Appreciate for your help. However, the result is still not what I need. I need to list all jobcodes for each emp_id in a row like:

    Code:
    empid jobid_1000 jobid_2000 jobid_3000 jobid_4000 jobid_5000
    ------- ------------- ------------ ------------- ------------ -------------
    1000                    A2             A3             A4
    1001                    A2                              A4
    ...
    I think I don't need the group-by-max clause. But I simply remove it, the result is not correct.

    I have played around with decode function. However, I still can't get it. Would you please kindly offer more help?
    Cheers,
    Shev

  7. #7
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56

    Talking

    I am sorry, I have made a mistake myself. I am now able to get the correct result from The_Duck. Thanks.
    Cheers,
    Shev

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    Originally posted by shev
    I am sorry, I have made a mistake myself. I am now able to get the correct result from The_Duck. Thanks.
    Whoo-hooo!!
    We did it!!

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56

    Arrow

    Just curious to know if there is any ways to reverse the operation, that is from column back to row?

    I think I can make use of a temp table and insert a row from each column. But this seems not a good method.

    Anyone has suggestion for me? Thank you.
    Cheers,
    Shev

  10. #10
    Join Date
    Feb 2004
    Location
    India
    Posts
    5
    Please check this for column to row

    take the example of dept table of scott schema

    select * from dept;

    DEPTNO DNAME LOC
    --------- -------------- -------------
    10 ACCOUNTING NEW YORK
    20 RESEARCH DALLAS
    30 SALES CHICAGO
    40 OPERATIONS BOSTON

    select y.deptno,decode( r, 2, y.dname, 3, y.loc ) column_to_row
    from dept y, (select rownum r,deptno from dept x where rownum <= 3 )
    where decode( r, 2, y.dname, 3, y.loc ) is not null;

    DEPTNO COLUMN_TO_ROW
    --------- --------------
    10 ACCOUNTING
    20 RESEARCH
    30 SALES
    40 OPERATIONS
    10 NEW YORK
    20 DALLAS
    30 CHICAGO
    40 BOSTON

    Let me know if it works.

    Thanks and Regards,
    SANG GARG

  11. #11
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56

    Arrow

    Thanks SANG_GARG, it works.

    Would you mind explain a little bit on the logic? I get stuck on it.

    Also, can this sql logic be able to apply on Access or SQL server? Seems there is ROWNUM which cannot be used in either Access or SQL, right?
    Cheers,
    Shev

Posting Permissions

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