8 Replies Latest reply on Feb 12, 2015 7:34 AM by Srinivasan S

    Table Column display format

    902923


      Hi Experts,

       

      We need to insert row values in to column of another table , could you please guide how can be solve this ? oracle version is 11.2.0.3

       

      for example

       

      we select rows from table Account

       

      CODE

      --------

      NYC

      KSA

       

      This two values needs to be inserted in to another tgt_account table ,

      like

       

      CODE_1   CODE_2

      -----------  -------------

      NYC         KSA

       

       

      Please suggest

       

      Thanks,

      Arun

        • 1. Re: Table Column display format
          2683628

          Hi

           

          Can you provide sample table definitions ?

          Also some test data

          • 2. Re: Table Column display format
            2683628

            what is the logic in merging the records

            • 3. Re: Table Column display format
              Srinivasan S

              Based on the input provided, please try this

               

              INSERT INTO tgt_account 

              SELECT * FROM ACCOUNT

              pivot (min(code) for code in ('NYC','KSA'));

               

              If you do not want to hard code the list, use dynamic query.

              PL/SQL 101 : Cursors and SQL Projection

              • 4. Re: Table Column display format
                902923

                Hi All,

                 

                Thanks for the response , the actual result is below ,

                 

                select currencycode from currency ccy, ( select currencyfrom_fk,currencyto_fk from currencyconversion where id =  '1085002' ) cnv

                where (ccy.id = cnv.currencyfrom_fk or ccy.id = cnv.currencyto_fk )

                 

                output is

                 

                CURRENCYCODE

                ------------------------

                NYC

                USC

                 

                This output needs to be converted as row to column format

                 

                like

                 

                A            B

                ---         -----

                NYC      USC

                 

                i tried the below Pivot method but its not capturing values , retuning null value only

                 

                select  * from (

                select 'data' val, currencycode from currency ccy, ( select ccyF_fk,ccyT_fk from ccyconversion where id =  '7845245' ) cnv

                where (ccy.id = cnv.ccyF_fk or ccy.id = cnv.ccyT_fk )

                ) res

                PIVOT

                ( max(val) for (currencycode) IN ('A' as a, 'B' as b))

                 

                output

                 

                A          B

                ---      -----

                (null )    (null)

                 

                Please help to achive this

                 

                Thanks,

                Arun.

                • 5. Re: Table Column display format
                  Srinivasan S

                  try this

                  select  * from (

                  select 'data' val, currencycode from currency ccy, ( select ccyF_fk,ccyT_fk from ccyconversion where id =  '7845245' ) cnv

                  where (ccy.id = cnv.ccyF_fk or ccy.id = cnv.ccyT_fk )

                  ) res

                  PIVOT

                  ( max(val) for (currencycode) IN ('NYC' as a, 'USC' as b))

                  • 6. Re: Table Column display format
                    902923

                    Hi srini,

                     

                    Thanks but instead of giving output directly in pivot (NYC and USC)we need to get the ouput from above query and need to pivot that value?

                    • 7. Re: Table Column display format
                      902923

                      Experts,

                       

                      Any help on this please?

                       

                      Thanks

                      Annamalai

                      • 8. Re: Table Column display format
                        Srinivasan S

                        Hi Annamalai,

                        The correct solution is to use dynamic pivoting a given below

                        PL/SQL 101 : Cursors and SQL Projection

                         

                        Quicker solution

                        Step 1 - Get the list of values 'NYC','USC' into a variable using stragg &

                        Step 2 - Use a dynamic query (use USING for binding) & use the above variable to list the values inside pivot

                         

                        SELECT STRAGG(CURRENCYCODE|| ',') INTO LV_FROM FROM CURRENCY CCY,

                        ( SELECT CCYF_FK,CCYT_FK FROM CCYCONVERSION WHERE ID =  '7845245' ) CNV

                        where (ccy.id = cnv.ccyF_fk or ccy.id = cnv.ccyT_fk );

                         

                        'select  * from (

                        select currencycode from currency ccy, ( select ccyF_fk,ccyT_fk from ccyconversion where id =  ''7845245'' ) cnv

                        where (ccy.id = cnv.ccyF_fk or ccy.id = cnv.ccyT_fk )

                        ) res

                        PIVOT

                        ( max(val) for (currencycode) IN (' || LV_FROM || '))'