8 Replies Latest reply: Feb 26, 2013 10:59 PM by 894936 RSS

    CONVERT

    894936
      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
          Please specify your os and oracle version.
          • 2. Re: CONVERT
            894936
            Hi Iam using oracle 10g. and windows xp
            • 3. Re: CONVERT
              Manik
              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
                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
                  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
                    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
                      Hi ,
                      Thank you.....
                      Thanks,
                      MR.
                      • 8. Re: CONVERT
                        894936
                        Hi Team,

                        Thanks for your help.

                        Thanks,
                        MR