This discussion is archived
8 Replies Latest reply: Feb 26, 2013 8:59 PM by 894936 RSS

CONVERT

894936 Newbie
Currently Being Moderated
Hi,
I need the below output.

my table consists of below structure: TABLE1.
Coulmn1   Values
med_no        1
Credit            x
Owned_by     x


I want the output like below.
Med_no     Credit     Owned_by
1                x              x
  • 1. Re: CONVERT
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    Please specify your os and oracle version.
  • 2. Re: CONVERT
    894936 Newbie
    Currently Being Moderated
    Hi Iam using oracle 10g. and windows xp
  • 3. Re: CONVERT
    Manik Expert
    Currently Being Moderated
    Try this way:
    WITH t AS
            (SELECT 'med_no' Coulmn1, '1' Val FROM DUAL
             UNION ALL
             SELECT 'Credit', 'x' FROM DUAL
             UNION ALL
             SELECT 'Owned_by', 'x' FROM DUAL)
    SELECT MAX (CASE WHEN coulmn1 = 'med_no' THEN val ELSE NULL END) med_no,
           MAX (CASE WHEN coulmn1 = 'Credit' THEN val ELSE NULL END) Credit,
           MAX (CASE WHEN coulmn1 = 'Owned_by' THEN val ELSE NULL END) Owned_by
      FROM t;
    output:
    MED_NO     CREDIT     OWNED_BY
    1     x     x
    Cheers,
    Manik.
  • 4. Re: CONVERT
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    Hi,

    In addition to manik's reply visit good link below

    http://www.oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1.php
  • 5. Re: CONVERT
    EdStevens Guru
    Currently Being Moderated
    891933 wrote:
    Hi,
    I need the below output.

    my table consists of below structure: TABLE1.
    Coulmn1   Values
    med_no        1
    Credit            x
    Owned_by     x
    
    
    I want the output like below.
    Med_no     Credit     Owned_by
    1                x              x
    It's not clear if you are showing us the structure or the contents of the table. But it looks to me as if you are showing us the contents. If that's the case, what defines the relationship between 'med_no' and 'Credit' and 'Owned_by'?
    On the other hand, if you are showing us the structure, your query would simply by
    select med_no,
           credit,
           owned_by
    from mytablename
  • 6. Re: CONVERT
    BluShadow Guru Moderator
    Currently Being Moderated
    891933 wrote:
    Hi,
    I need the below output.

    my table consists of below structure: TABLE1.
    Coulmn1   Values
    med_no        1
    Credit            x
    Owned_by     x
    That looks like a very bad database design, almost equivalent to an Entity-Attribute-Value model.

    Storing data that way in tables defeats the purpose of relational database tables, making code hard to write and maintain as well as introducing performance problems and preventing proper data integrity.

    Why is your table designed to store different types of values in the same columns rather than a traditional relational 3NF table structure?
  • 7. Re: CONVERT
    894936 Newbie
    Currently Being Moderated
    Hi ,
    Thank you.....
    Thanks,
    MR.
  • 8. Re: CONVERT
    894936 Newbie
    Currently Being Moderated
    Hi Team,

    Thanks for your help.

    Thanks,
    MR

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points