Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2013
    Posts
    24

    Unanswered: convert column values to row

    Hi,

    I am working on oracle10g.
    I am fetching a row which has 5 columns.
    I want to fetch this in rows.

    SELECT empno, empname,sal,job,comm FROM emp where empno=3005;
    This gives output as

    EMPNO EMPNAME SAL JOB COMM
    ------------------------------------
    3005 JOHN 50000 MANAGER 5000

    However my expected output is

    column_name value
    --------------------
    EMPNO 3005
    EMPNAME JOHN
    SAL 50000
    JOB MANAGER
    COMM 5000


    Can you Please suggest how to do this?

    -- P J S

  2. #2
    Join Date
    Dec 2007
    Posts
    253
    You could use the union operator

  3. #3
    Join Date
    Jul 2013
    Posts
    24
    as per my understanding, union is used to combine outputs of multiple queries.

    I want to rearrange the output so that 5 columns in a single row are shown in 5 rows.

    --P J S

  4. #4
    Join Date
    Dec 2007
    Posts
    253
    Yes, for that, create 5 different queries, one for each column, and combine them using union.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you were using Oracle 11g you could use the PIVOT operator. In 10g you'll have to use the example for previous versions which is provided about halfway through that web page.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Dec 2007
    Posts
    253
    I presume you meant that the OP could use UNPivot?

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by pablolee View Post
    I presume you meant that the OP could use UNPivot?
    Ooops!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Dec 2007
    Posts
    253
    We knew what you meant

  9. #9
    Join Date
    Jul 2013
    Posts
    24
    I am working on 10g... Unpivot can be used in 11g... am I right?
    -P J S

  10. #10
    Join Date
    Dec 2007
    Posts
    253
    Yes, but towards the bottom, there is a non 11g possible implementation

  11. #11
    Join Date
    Jul 2013
    Posts
    24
    It is just Superb..
    I implemented it using method given on web page told by you, using decode function
    Its working.. Thanks a lot...Pat.

    I had tried a lot for this. Now it is done. Thanks to you..

    --P J S

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ah PabloLee did all of the heavy lifting, I just supplied the idea and the URL.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #13
    Join Date
    Jul 2013
    Posts
    24
    Oh... Thanks for you help..pablolee.
    --P J S

  14. #14
    Join Date
    Jul 2013
    Posts
    24
    Hi,

    I am working on oracle 10g.
    Solution given by you on above case worked perfectly to convert several columns into rows in one column.
    I have come across another case where again I have to convert columns into rows.
    However, Here out of 6, I need here 3 columns to be converted into 3 rows of single column
    and remaining 3 columns into another 3 rows of other column.
    i.e.

    Existing row:

    IDBI_savings ICICI_savings SBI_savings IDBI_current ICICI_current SBI_current
    50000 60000 40000 20000 30000 20000
    60000 30000 50000 10000 20000 20000

    Expected output
    savings
    IDBI_savings 50000 IDBI_current 20000
    ICICI_savings 60000 ICICI_current 30000
    SBI_savings 40000 SBI_current 20000
    IDBI_savings 60000 IDBI_current 10000
    ICICI_savings 30000 ICICI_current 20000
    SBI_savings 50000 SBI_current 20000

    Can you please suggest, how to do this?

    -- P J S

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is an example, but not tested on Oracle.
    (Tested on DB2.)

    You might want to make an amendment to meet to Oracle's syntax.

    Code:
    SELECT savings ||
           CASE savings
           WHEN 'IDBI_savings'  THEN
                 IDBI_savings
           WHEN 'ICICI_savings' THEN
                 ICICI_savings
           WHEN 'SBI_savings'   THEN
                 SBI_savings
           END  AS savings
         , current ||
           CASE current
           WHEN 'IDBI_current'  THEN
                 IDBI_current
           WHEN 'ICICI_current' THEN
                 ICICI_current
           WHEN 'SBI_current'   THEN
                 SBI_current
           END  AS current
     FROM  (SELECT t.*
                 , ROW_NUMBER() OVER() AS r_num
             FROM  <<your table>> AS t
           )
         , (SELECT 1 , 'IDBI_savings  ' , 'IDBI_current  ' FROM dual UNION ALL
            SELECT 2 , 'ICICI_savings ' , 'ICICI_current ' FROM dual UNION ALL
            SELECT 3 , 'SBI_savings   ' , 'SBI_current   ' FROM dual
           ) AS p( j , savings , current )
     ORDER BY
           r_num
         , j
    ;

Posting Permissions

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