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

    unpivot function


      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

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



          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


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

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



                      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





          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