1 Reply Latest reply: Mar 7, 2014 5:19 AM by BluShadow RSS

    Locked thread: Dynamic column name using cursor in Oracle PLSQL

    Etbin

      Dynamic column name using cursor in Oracle PLSQL


      Are you after something like below ?

       

      create table test as

      select level row_id,

             dbms_random.string('u',dbms_random.value(3,15)) name11,

             dbms_random.string('u',dbms_random.value(3,15)) name12,

             dbms_random.string('u',dbms_random.value(3,15)) name13,

             dbms_random.string('u',dbms_random.value(3,15)) name14,

             dbms_random.string('u',dbms_random.value(3,15)) name15

        from dual

      connect by level <= 10

       

      ROW_IDNAME11NAME12NAME13NAME14NAME15
      1WIOKXPBFAPKMZI  VRMJTT          SKTERZCB         DFGMQMJFJOZXBAR  ARIXGGIJQMF
      2ARBIGWW         MRNF            MGUUDK           LNPFJKJU         MVQELOQKVZOS
      3XGDOS           KTT             ZUPJ             KCZI             TQDCVQHFINI
      4QPBHGTE         VSGK            GVR              SOTEVHSWLU       OSCK
      5BCVDHNYSODT     YTLJWYNIPANAVI  LGNMTIGDNIQI     RMCUAE           HTOXHD
      6SXKQPOALMXKWLH  TQNLQDQF        IPXRTKMFRTWS     JCUE             JOBL
      7WVEFAXISAMIHIY  QDQGPNQMN       PUEFBCQEUDSQS    FDBEA            WTX
      8IYCSYLSLZKGYK   FGLZZQR         HSLQNNGLQXTHYZ   IPTNYPDNL        NEZQNQEO
      9OPFEWJITREYY    VAZYOUEIRGP     EAWPERIWDE       SYMECOBF         KNCOLS
      10WUIFUGBLOZBOIC  JTMEACW         OCTREPTV         ZZNRKFVG         OTKQKPAXRCRHDN

       

      select row_id,name11,name12,name13,name14,name15

        from (select row_id,

                     col_id,

                     case when mod(row_id,2) != mod(col_id,2)

                          then lower(the_value)

                          else initcap(the_value)

                     end || '(' || to_char(length(the_value)) || ')' processed

                from test

              unpivot (the_value for col_id in (name11 as 1,name12 as 2,name13 as 3,name14 as 4,name15 as 5))

             )

      pivot (max(processed) for col_id in (1 as name11,2 as name12,3 as name13,4 as name14,5 as name15))

       

      ROW_IDNAME11NAME12NAME13NAME14NAME15
      1Wiokxpbfapkmzi(14) vrmjtt(6)          Skterzcb(8)         dfgmqmjfjozxbar(15)Arixggijqmf(11)
      2arbigww(7)         Mrnf(4)           mguudk(6)           Lnpfjkju(8)         mvqeloqkvzos(12)
      3Xgdos(5)         ktt(3)            Zupj(4)            kczi(4)            Tqdcvqhfini(11)
      4qpbhgte(7)         Vsgk(4)           gvr(3)              Sotevhswlu(10)       osck(4)
      5Bcvdhnysodt(11)     ytljwynipanavi(14)  Lgnmtigdniqi(12)     rmcuae(6)          Htoxhd(6)
      6sxkqpoalmxkwlh(14)  Tqnlqdqf(8)       ipxrtkmfrtws(12)     Jcue(4)            jobl(4)
      7Wvefaxisamihiy(14)  qdqgpnqmn(9)      Puefbcqeudsqs(13)    fdbea(5)            Wtx(3)
      8iycsylslzkgyk(13)   Fglzzqr(7)         hslqnnglqxthyz(14)  Iptnypdnl(9)        nezqnqeo(8)
      9Opfewjitreyy(12)   vazyoueirgp(11)     Eawperiwde(10)      symecobf(8)         Kncols(6)
      10wuifugblozboic(14)  Jtmeacw(7)        octreptv(8)         Zznrkfvg(8)         otkqkpaxrcrhdn(14)

       

      Regards

       

      Etbin