7 Replies Latest reply: Feb 13, 2013 4:23 AM by John Stegeman RSS

    Join 2 Tables on Column Name and Row Field Value

    963852
      Can we join Tables on the basis of Column Name from one table and Row Field Value from other table using SQL?

      Eg: Table A having columns Name & ID
      Name Dept
      a Finance
      b Manufacturing
      c Quality

      Table 2 having columns Group & different Depts(as per table A) as columns
      Group Finance Manufacturing Qualty
      A 1 5 2
      B 3 7 9
        • 1. Re: Join 2 Tables on Column Name and Row Field Value
          _Karthick_
          Welcome to the fourm!!!

          Please consider the following when you post a question. This would help us help you better

          1. New features keep coming in every oracle version so please provide Your Oracle DB Version to get the best possible answer.

          You can use the following query and do a copy past of the output.
           
          select * from v$version 
          2. This forum has a very good Search Feature. Please use that before posting your question. Because for most of the questions
          that are asked the answer is already there.

          3. We dont know your DB structure or How your Data is. So you need to let us know. The best way would be to give some sample data like this.
           
          I have the following table called sales 
               
          with sales 
          as 
          ( 
                select 1 sales_id, 1 prod_id, 1001 inv_num, 120 qty from dual 
                union all 
                select 2 sales_id, 1 prod_id, 1002 inv_num, 25 qty from dual 
          ) 
          select * 
            from sales 
          4. Rather than telling what you want in words its more easier when you give your expected output.

          For example in the above sales table, I want to know the total quantity and number of invoice for each product.

          The output should look like this
           
          Prod_id   sum_qty   count_inv 
          ------------------------------ 
          1         145       2 
          5. When ever you get an error message post the entire error message. With the Error Number, The message and the Line number.

          6. Next thing is a very important thing to remember. Please post only well formatted code. Unformatted code is very hard to read.

          Your code format gets lost when you post it in the Oracle Forum. So in order to preserve it you need to
          use the {noformat}
          {noformat} tags. 
               
          The usage of the tag is like this. 
               
          \
          <place your code here>
          \
           
              
          7. If you are posting a *Performance Related Question*. Please read 
             {thread:id=501834} and {thread:id=863295}. 
             Following those guide will be very helpful.
              
          8. Please keep in mind that this is a public forum. Here No question is URGENT. 
             So use of words like *URGENT* or *ASAP* (As Soon As Possible) are considered to be rude.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
          • 2. Re: Join 2 Tables on Column Name and Row Field Value
            963852
            The table structure is as below
            OID Region
            1 Americas
            2 Asia Pac
            3 Europe
            4 Japan


            SNo Americas AsiaPac Europe Japan
            1 1 2 3 4
            2 2 3 4 5
            4 3 4 5 6

            I need to join based on region to select values from table 2.
            eg: to fetch values under Americas
            • 3. Re: Join 2 Tables on Column Name and Row Field Value
              pollywog
              if I'm understanding correctly just pivot your table
              with t as 
              (select  1 OID,  'Americas' region from dual union all
               select  2, 'Asia Pac' from dual union all
               select  3, 'Europe' from dual union all
               select 4, 'Japan' from dual
               )
               select * from t
               pivot
               ( max(oid)
               for  region in ('Americas' as Americas ,'Asia Pac' as AsiaPac,'Europe' as Europe,'Japan' as Japan)
               );
               
              AMERICAS     ASIAPAC     EUROPE     JAPAN
              1     2     3     4
              • 4. Re: Join 2 Tables on Column Name and Row Field Value
                Peter vd Zwan
                hi,

                Is this what you need?
                with a (OID, Region) as
                (
                select 1, 'Americas' from dual union all
                select 2, 'Asia Pac' from dual union all
                select 3, 'Europe' from dual union all
                select 4, 'Japan' from dual
                )
                ,b (SNo, Americas, AsiaPac, Europe, Japan) as
                (
                select 1, 1, 2, 3, 4 from dual union all
                select 2, 2, 3, 4, 5 from dual union all
                select 4, 3, 4, 5, 6 from dual
                )
                
                select
                  a.oid
                  ,a.region
                  ,case a.region
                        when 'Americas' then b.Americas
                        when 'Asia Pac' then b.AsiaPac
                        when 'Europe'   then b.Europe
                        when 'Japan'    then b.Japan
                        else null
                  end region_value
                
                from
                  a left outer join
                  b on (a.oid = b.sno)
                
                order by
                  a.oid
                
                ;
                
                OID REGION   REGION_VALUE
                --- -------- ------------
                  1 Americas            1 
                  2 Asia Pac            3 
                  3 Europe                
                  4 Japan               6 
                Regards,

                Peter
                • 5. Re: Join 2 Tables on Column Name and Row Field Value
                  User583321-Oracle
                  Hi All

                  I have a similar requirement , but i can't hard code column names as there are more than 200 columns.
                  Oracle DB version is 11.2.0.3.0.



                  Any pointers highly appreciated.



                  Thanks
                  • 6. Re: Join 2 Tables on Column Name and Row Field Value
                    BluShadow
                    user583321 wrote:
                    Hi All

                    I have a similar requirement , but i can't hard code column names as there are more than 200 columns.
                    Why? Too much typing for you?
                    • 7. Re: Join 2 Tables on Column Name and Row Field Value
                      John Stegeman
                      but i can't hard code column names
                      Yes, and my database has a few hundred tables, so I can't hard-code any SQL statements either

                      </sarcasm_mode>