1 Reply Latest reply on Jul 19, 2013 12:52 PM by Etbin

    unpivot function

    930854

      Hi all,

       

      My requirement is to compare the the two records and identify by different values column names.

       

      ex:  row1  row2  row3

              1      2        3

              2      2       4

       

      output:  row2

       

       

      the above ex shows the row2 column values is same,so the output is row2.

       

      I am using unpivot funtion to retrieve this data. can anyone explain what is the advantage and disadvantage of unpivot function.

        • 1. Re: unpivot function
          Etbin

          You might try using model (most probably slower - that might be the advanage of unpivot)

           

          with

          the_data as

          (select 1 c1,2 c2,3 c3,4 c4,5 c5 from dual union all

          select 2,2,3,2,2 from dual

          )

          select col_list

            from (select c1,c2,c3,c4,c5,rn,rtrim(col_list,',') col_list

                    from the_data

                   model

                     dimension by (row_number() over (order by null) rn)

                     measures (c1,c2,c3,c4,c5,cast('equal_columns => ' as varchar2(4000)) col_list)

                     rules

                     (

                      col_list[0] = col_list[1] || case when c1[1] = c1[2] then 'C1,' end,

                      col_list[0] = col_list[0] || case when c2[1] = c2[2] then 'C2,' end,

                      col_list[0] = col_list[0] || case when c3[1] = c3[2] then 'C3,' end,

                      col_list[0] = col_list[0] || case when c4[1] = c4[2] then 'C4,' end,

                      col_list[0] = col_list[0] || case when c5[1] = c5[2] then 'C5' end

                     )

                 )

          where rn = 0

           

          Regards

           

          Etbin


          another method could be


          select listagg(the_column,',') within group (order by the_column) the_list

            from (select 'C'||lpad(level,2,'0') the_column,

                         case when substr(one,level,1) = substr(two,level,1) then 'YES' else 'NO' end equal_columns

                    from (select '1234567890' one,'0287657821' two

                            from dual

                         )

                  connect by level <= length(one)

                 )

          where equal_columns = 'YES'

           

          Message was edited by: Etbin