1 2 Previous Next 25 Replies Latest reply: Jul 24, 2014 7:00 PM by Etbin Go to original post RSS
      • 15. Re: Generate exception on column comparison
        2716340

        Sorry If I didn't explain it properly....Basically for the result we can only have 5 columns for the List1 and 5 columns for the List2.So after the matching columns the rest should be adjusted in the rest of the columns.Here within 5_ID_A,5_ID_B and 5_NAME_A,5_NAME_B we should have all the values listed from LIST1 and LIST2.For the matching we can post the exception as "Matching" and the rest can be "Non-Matching"

        • 16. Re: Generate exception on column comparison
          2716340

          • Just to add to the above details..Here all the columns should stay with RN=5.
          • 991 is matching so need to place as shown in the result which is working fine.
          • The rest 216 IDs (all of them though there is duplicates) with the names should be placed against 161,213,215,484 in any order so we are only using 5 columns for each list
          • 17. Re: Re: Generate exception on column comparison
            Peter vd Zwan


            Hi,

             

            OK now I understand.

            Try the below:

             

            WITH

               list_A AS

               (SELECT

                 777           AS DEPT_NO

               , 161           AS LIST1_EMP_ID1

               , 'Christopher' AS LIST1_EMP_NAME1

               , 213           AS LIST1_EMP_ID2

               , 'Gerding'     AS LIST1_EMP_NAME2

               , 215           AS LIST1_EMP_ID3

               , 'Joseph'      AS LIST1_EMP_NAME3

               , 484           AS LIST1_EMP_ID4

               , 'Janel'       AS LIST1_EMP_NAME4

               , 991           AS LIST1_EMP_ID5

               , 'Kevin'       AS LIST1_EMP_NAME5

               FROM

                 dual

               )

            ,list_B AS

               (SELECT

                 777        AS DEPT_NO

               , 991        AS LIST1_EMP_ID1

               , 'Kevin'    AS LIST1_EMP_NAME1

               , 216        AS LIST1_EMP_ID2

               , 'Ricky'    AS LIST1_EMP_NAME2

               , 216        AS LIST1_EMP_ID3

               , 'Ricky'    AS LIST1_EMP_NAME3

               , 216        AS LIST1_EMP_ID4

               , 'Ricky'    AS LIST1_EMP_NAME4

               , 216        AS LIST1_EMP_ID5

               , 'Ricky B ' AS LIST1_EMP_NAME5

               FROM

                 dual

               )

            , match_1 AS

               (SELECT

                 COALESCE(a.dept_no, b.dept_no)          DEPT_NO      --a.dept_no

               ,a.id id_a

               ,b.id id_b

               ,DECODE(a.id, b.id, 'Matching', 'not matching') id_exception

               ,a.name name_a

               ,b.name name_b

               ,DECODE(a.name, b.name, 'Matching', 'not matching') name_exception

            --   ,COUNT(*) over (partition BY COALESCE(a.dept_no, b.dept_no) order by COALESCE(a.dept_no, b.dept_no), a.id_no, b.id_no) rn

               ,COUNT(DECODE(a.id, b.id, 1)) over (partition BY COALESCE(a.dept_no, b.dept_no) order by COALESCE(a.dept_no, b.dept_no), DECODE(b.id, a.id_no,0,1))             

                  + nvl(DECODE(b.id, null, count(*) over (partition BY COALESCE(a.dept_no, b.dept_no) order by DECODE(b.id,null, a.id_no)) ),0)  

                  + nvl(DECODE(a.id, null, count(*) over (partition BY COALESCE(a.dept_no, b.dept_no) order by DECODE(a.id,null, b.id_no)) ),0)     rn

               FROM

                 list_A unpivot ( (id,name) FOR (id_no,name_no ) IN ( (list1_emp_id1, list1_emp_name1) AS (1,1)

                                                                     ,(list1_emp_id2, list1_emp_name2) AS (2,2)

                                                                    ,(list1_emp_id3, list1_emp_name3) AS (3,3)

                                                                    ,(list1_emp_id4, list1_emp_name4) AS (4,4)

                                                                    ,(list1_emp_id5, list1_emp_name5) AS (5,5)

                                                                    )

                              ) a

            FULL OUTER JOIN

                list_B unpivot ( (id,name) FOR (id_no,name_no ) IN ( (list1_emp_id1, list1_emp_name1) AS (1,1)

                                                                   ,(list1_emp_id2, list1_emp_name2) AS (2,2)

                                                                    ,(list1_emp_id3, list1_emp_name3) AS (3,3)

                                                                    ,(list1_emp_id4, list1_emp_name4) AS (4,4)

                                                                   ,(list1_emp_id5, list1_emp_name5) AS (5,5)

                                                                   )

                               ) b ON ( b.dept_no = a.dept_no AND b.id = a.id)

            )

            SELECT

              *

            FROM

              match_1 pivot

                  (  MAX(id_a) id_a

                                   ,MAX(id_b) id_b

                                    ,MAX(id_exception) id_exception

                                    ,MAX(name_a) name_a

                                    ,MAX(name_b) name_b

                                    ,MAX(name_exception) name_exception FOR rn IN (1,2,3,4,5))   --,6,7,8,9,10) ) --) )

            --WHERE

            --  dept_no IS NOT NULL

            ;

             

            Regards,

             

            Peter

            • 18. Re: Generate exception on column comparison
              2716340

              Hi Peter,

               

              The result should look like before the last PIVOT

               

              Here except for the matching ID_A and ID_B all the ID_A and ID_B can just be loaded in any which order as they don't match each other.The key is to use only columns after the PIVOT for each list.

                     

              DEPT_NOID_AID_BID_EXCEPTIONNAME_ANAME_BNAME_EXCEPTIONRN
              777161216not matchingChristopherRickynot matching1
              777213216not matchingGerdingRickynot matching2
              777215216not matchingJosephRickynot matching3
              777484216not matchingJanelRicky B not matching4
              777991991MatchingKevinKevinMatching5
              • 19. Re: Generate exception on column comparison
                2716340

                Sorry I posted just after your post....Thanks a lot for the help..Let me check against the bigger data set and get back to you...

                • 20. Re: Generate exception on column comparison
                  2716340

                  Hi Peter,

                   

                  When I changed the listing it didn't work for this scenario..Before the PIVOT it has captured all the "matching and Non-Matching" correctly but looks like the RN value is messed up...

                   

                   

                   

                      WITH

                   

                         list_A AS

                   

                         (SELECT

                   

                           777           AS DEPT_NO

                   

                         , 161           AS LIST1_EMP_ID1

                   

                         , 'Christopher' AS LIST1_EMP_NAME1

                   

                         , 213           AS LIST1_EMP_ID2

                   

                         , 'Gerding'     AS LIST1_EMP_NAME2

                   

                         , 215           AS LIST1_EMP_ID3

                   

                         , 'Joseph'      AS LIST1_EMP_NAME3

                   

                         , 484           AS LIST1_EMP_ID4

                   

                         , 'Janel'       AS LIST1_EMP_NAME4

                   

                         , 991           AS LIST1_EMP_ID5

                   

                         , 'Kevin'       AS LIST1_EMP_NAME5

                   

                         FROM

                   

                           dual

                   

                         )

                   

                      ,list_B AS

                   

                         (SELECT

                   

                           777        AS DEPT_NO

                   

                         , 991        AS LIST1_EMP_ID1

                   

                         , 'Kevin'    AS LIST1_EMP_NAME1

                   

                         , 161        AS LIST1_EMP_ID2

                   

                         , 'Ricky'    AS LIST1_EMP_NAME2

                   

                         , 213        AS LIST1_EMP_ID3

                   

                         , 'Ricky'    AS LIST1_EMP_NAME3

                   

                         , 215        AS LIST1_EMP_ID4

                   

                         , 'Ricky'    AS LIST1_EMP_NAME4

                   

                         , 216        AS LIST1_EMP_ID5

                   

                         , 'Ricky B ' AS LIST1_EMP_NAME5

                   

                         FROM

                   

                           dual

                   

                         )

                   

                      , match_1 AS

                   

                         (SELECT

                   

                           COALESCE(a.dept_no, b.dept_no)          DEPT_NO      --a.dept_no

                   

                         ,a.id id_a

                   

                         ,b.id id_b

                   

                         ,DECODE(a.id, b.id, 'Matching', 'not matching') id_exception

                   

                         ,a.name name_a

                   

                         ,b.name name_b

                   

                         ,DECODE(a.name, b.name, 'Matching', 'not matching') name_exception

                   

                      --   ,COUNT(*) over (partition BY COALESCE(a.dept_no, b.dept_no) order by COALESCE(a.dept_no, b.dept_no), a.id_no, b.id_no) rn

                   

                         ,COUNT(DECODE(a.id, b.id, 1)) over (partition BY COALESCE(a.dept_no, b.dept_no) order by COALESCE(a.dept_no, b.dept_no), DECODE(b.id, a.id_no,0,1))            

                   

                            + nvl(DECODE(b.id, null, count(*) over (partition BY COALESCE(a.dept_no, b.dept_no) order by DECODE(b.id,null, a.id_no)) ),0) 

                   

                            + nvl(DECODE(a.id, null, count(*) over (partition BY COALESCE(a.dept_no, b.dept_no) order by DECODE(a.id,null, b.id_no)) ),0)     rn

                   

                         FROM

                   

                           list_A unpivot ( (id,name) FOR (id_no,name_no ) IN ( (list1_emp_id1, list1_emp_name1) AS (1,1)

                   

                                                                               ,(list1_emp_id2, list1_emp_name2) AS (2,2)

                   

                                                                              ,(list1_emp_id3, list1_emp_name3) AS (3,3)

                   

                                                                              ,(list1_emp_id4, list1_emp_name4) AS (4,4)

                   

                                                                              ,(list1_emp_id5, list1_emp_name5) AS (5,5)

                   

                                                                              )

                   

                                        ) a

                   

                      FULL OUTER JOIN

                   

                          list_B unpivot ( (id,name) FOR (id_no,name_no ) IN ( (list1_emp_id1, list1_emp_name1) AS (1,1)

                   

                                                                             ,(list1_emp_id2, list1_emp_name2) AS (2,2)

                   

                                                                              ,(list1_emp_id3, list1_emp_name3) AS (3,3)

                   

                                                                              ,(list1_emp_id4, list1_emp_name4) AS (4,4)

                   

                                                                             ,(list1_emp_id5, list1_emp_name5) AS (5,5)

                   

                                                                             )

                   

                                         ) b ON ( b.dept_no = a.dept_no AND b.id = a.id)

                   

                      )

                   

                      SELECT

                   

                        *

                   

                      FROM

                   

                        match_1 pivot

                   

                            (  MAX(id_a) id_a

                   

                                             ,MAX(id_b) id_b

                   

                                              ,MAX(id_exception) id_exception

                   

                                              ,MAX(name_a) name_a

                   

                                              ,MAX(name_b) name_b

                   

                                              ,MAX(name_exception) name_exception FOR rn IN (1,2,3,4,5))   --,6,7,8,9,10) ) --) )

                   

                      --WHERE

                   

                      --  dept_no IS NOT NULL

                   

                      ;

                  • 21. Re: Re: Generate exception on column comparison
                    Peter vd Zwan


                    Hi,

                     

                    try this:

                    WITH
                           list_A AS
                           (SELECT
                             777           AS DEPT_NO
                           , 161           AS LIST1_EMP_ID1
                           , 'Christopher' AS LIST1_EMP_NAME1
                           , 213           AS LIST1_EMP_ID2
                           , 'Gerding'     AS LIST1_EMP_NAME2
                           , 215           AS LIST1_EMP_ID3     --
                           , 'Joseph'      AS LIST1_EMP_NAME3
                           , 484           AS LIST1_EMP_ID4     --
                           , 'Janel'       AS LIST1_EMP_NAME4
                           , 991           AS LIST1_EMP_ID5
                           , 'Kevin'       AS LIST1_EMP_NAME5

                           FROM
                             dual
                           )
                        ,list_B AS
                           (SELECT
                             777        AS DEPT_NO
                           , 991        AS LIST1_EMP_ID1
                           , 'Kevin'    AS LIST1_EMP_NAME1
                           , 161        AS LIST1_EMP_ID2        --
                           , 'Ricky'    AS LIST1_EMP_NAME2
                           , 213        AS LIST1_EMP_ID3
                           , 'Ricky'    AS LIST1_EMP_NAME3
                           , 215        AS LIST1_EMP_ID4
                           , 'Ricky'    AS LIST1_EMP_NAME4
                           , 216        AS LIST1_EMP_ID5
                           , 'Ricky B ' AS LIST1_EMP_NAME5
                           FROM
                             dual
                           )
                        , match_1 AS
                           (SELECT
                             COALESCE(a.dept_no, b.dept_no)          DEPT_NO      --a.dept_no
                           ,a.id id_a
                           ,b.id id_b
                           ,DECODE(a.id, b.id, 'Matching', 'not matching') id_exception
                           ,a.name name_a
                           ,b.name name_b
                           ,DECODE(a.name, b.name, 'Matching', 'not matching') name_exception
                    --       ,COUNT(DECODE(a.id, b.id, 1)) over (partition BY COALESCE(a.dept_no, b.dept_no) order by COALESCE(a.dept_no, b.dept_no), DECODE(b.id, a.id_no,0,1))            
                          ,COUNT(DECODE(a.id, b.id, 1)) over (partition BY COALESCE(a.dept_no, b.dept_no) order by COALESCE(a.dept_no, b.dept_no), case when b.id <> a.id_no then 0 else 1 end rows between unbounded preceding and current row )
                              + nvl(DECODE(b.id, null, count(*) over (partition BY COALESCE(a.dept_no, b.dept_no) order by DECODE(b.id,null, a.id_no)) ),0)
                              + nvl(DECODE(a.id, null, count(*) over (partition BY COALESCE(a.dept_no, b.dept_no) order by DECODE(a.id,null, b.id_no)) ),0)     rn
                           FROM
                             list_A unpivot ( (id,name) FOR (id_no,name_no ) IN ( (list1_emp_id1, list1_emp_name1) AS (1,1)
                                                                                 ,(list1_emp_id2, list1_emp_name2) AS (2,2)
                                                                                ,(list1_emp_id3, list1_emp_name3) AS (3,3)
                                                                                ,(list1_emp_id4, list1_emp_name4) AS (4,4)
                                                                                ,(list1_emp_id5, list1_emp_name5) AS (5,5)
                                                                                )
                                          ) a
                        FULL OUTER JOIN
                            list_B unpivot ( (id,name) FOR (id_no,name_no ) IN ( (list1_emp_id1, list1_emp_name1) AS (1,1)
                                                                               ,(list1_emp_id2, list1_emp_name2) AS (2,2)
                                                                                ,(list1_emp_id3, list1_emp_name3) AS (3,3)
                                                                                ,(list1_emp_id4, list1_emp_name4) AS (4,4)
                                                                               ,(list1_emp_id5, list1_emp_name5) AS (5,5)
                                                                               )
                                           ) b ON ( b.dept_no = a.dept_no AND b.id = a.id)
                        )
                        SELECT
                          *
                        FROM
                          match_1
                        pivot
                              (  MAX(id_a) id_a
                                               ,MAX(id_b) id_b
                                                ,MAX(id_exception) id_exception
                                                ,MAX(name_a) name_a
                                                ,MAX(name_b) name_b
                                                ,MAX(name_exception) name_exception FOR rn IN (1,2,3,4,5))   --,6,7,8,9,10) ) --) )
                        ;

                     

                    regards,

                     

                    Peter

                    • 22. Re: Generate exception on column comparison
                      2716340

                      Hi Peter..So far most of the scenarios I tested is working fine..Validating few more...One quick think..For the below list the  RN values are 2 and 4 instead of 1 and 2...

                       

                          WITH

                                 list_A AS

                                 (SELECT

                                   777           AS DEPT_NO

                                 , 161           AS LIST1_EMP_ID1

                                 , 'Christopher' AS LIST1_EMP_NAME1

                                 , NULL           AS LIST1_EMP_ID2

                                 , NULL     AS LIST1_EMP_NAME2

                                 , NULL           AS LIST1_EMP_ID3     --

                                 , NULL      AS LIST1_EMP_NAME3

                                 , NULL           AS LIST1_EMP_ID4     --

                                 , NULL       AS LIST1_EMP_NAME4

                                 , NULL           AS LIST1_EMP_ID5

                                 , NULL       AS LIST1_EMP_NAME5

                       

                                 FROM

                                   dual

                                 )

                              ,list_B AS

                                 (SELECT

                                   777        AS DEPT_NO

                                 , NULL        AS LIST1_EMP_ID1

                                 , 'Christopher'    AS LIST1_EMP_NAME1

                                 , NULL        AS LIST1_EMP_ID2        --

                                 , NULL    AS LIST1_EMP_NAME2

                                 , NULL        AS LIST1_EMP_ID3

                                 , NULL    AS LIST1_EMP_NAME3

                                 , NULL        AS LIST1_EMP_ID4

                                 , NULL    AS LIST1_EMP_NAME4

                                 , NULL        AS LIST1_EMP_ID5

                                 , NULL AS LIST1_EMP_NAME5

                                 FROM

                                   dual

                                 )

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

                       

                      Also here when there is NULLs on the IDs in list_B this is giving RN as 6...which on the pivot is going out from the list

                       

                          WITH

                                 list_A AS

                                 (SELECT

                                   777           AS DEPT_NO

                                 , 161           AS LIST1_EMP_ID1

                                 , 'Christopher' AS LIST1_EMP_NAME1

                                 , NULL           AS LIST1_EMP_ID2

                                 , NULL     AS LIST1_EMP_NAME2

                                 , NULL           AS LIST1_EMP_ID3     --

                                 , NULL      AS LIST1_EMP_NAME3

                                 , NULL           AS LIST1_EMP_ID4     --

                                 , NULL       AS LIST1_EMP_NAME4

                                 , NULL           AS LIST1_EMP_ID5

                                 , NULL       AS LIST1_EMP_NAME5

                       

                                 FROM

                                   dual

                                 )

                              ,list_B AS

                                 (SELECT

                                   777        AS DEPT_NO

                                 , NULL        AS LIST1_EMP_ID1

                                 , 'Christopher'    AS LIST1_EMP_NAME1

                                 , NULL        AS LIST1_EMP_ID2        --

                                 , 'Raj'    AS LIST1_EMP_NAME2

                                 , NULL        AS LIST1_EMP_ID3

                                 , NULL    AS LIST1_EMP_NAME3

                                 , NULL        AS LIST1_EMP_ID4

                                 , NULL    AS LIST1_EMP_NAME4

                                 , NULL        AS LIST1_EMP_ID5

                                 , NULL AS LIST1_EMP_NAME5

                                 FROM

                                   dual

                                 )

                      • 23. Re: Generate exception on column comparison
                        Peter vd Zwan

                        Hi,

                         

                        I wouold like to help you but to do so please make one test set for the data (with different dept_no for each posible scenario).

                        Make one resultset as you want to have it. If needed discribe for the different sets why it should be like that.

                        Then we can do some testing.

                        Otherwise we keep on changing this many times.

                         

                        Just a question: can you have more rows with the same dept_no in list_A or list_B?

                         

                         

                        Regards,

                        Peter

                        • 24. Re: Generate exception on column comparison
                          2716340

                          Sorry for that...Your help till this point has been so valuable....Sure I can work on the test data

                           

                          No there will be only one dept_no in each list....Cannot have more than one dept_no in List_A and List_B

                           

                          Here was the example for the above one..I will work on the test data...Also here if the IDs are both empty then the exception should be either left as empty or display as "Not available as there is a name with no ID....

                          • 25. Re: Generate exception on column comparison
                            Etbin

                            Not very sure about pivoting my final result:

                            do you want dept_no, table_name plus 11 column sets (item, id_value, match_result, name_value, match_result) on two rows ?

                            several subquery factors left in order to make checking intermediate results possible

                             

                            with

                            table1 as (

                            SELECT

                            777 AS DEPT_NO,

                            100 AS  LIST1_EMP_ID1,

                            'Raj' AS LIST1_EMP_NAME1,

                            101 AS  LIST1_EMP_ID2,

                            'Lee' AS LIST1_EMP_NAME2,

                            NULL AS  LIST1_EMP_ID3,

                            NULL AS LIST1_EMP_NAME3,

                            103 AS  LIST1_EMP_ID4,

                            'Sid' AS LIST1_EMP_NAME4,

                            105 AS  LIST1_EMP_ID5,

                            'Ann' AS LIST1_EMP_NAME5

                            FROM dual

                            UNION ALL

                            SELECT

                            888 AS DEPT_NO,

                            200 AS  LIST1_EMP_ID1,

                            'Betty' AS LIST1_EMP_NAME1,

                            202 AS  LIST1_EMP_ID2,

                            'James' AS LIST1_EMP_NAME2,

                            201 AS  LIST1_EMP_ID3,

                            'Jacob' AS LIST1_EMP_NAME3,

                            NULL AS  LIST1_EMP_ID4,

                            'John' AS LIST1_EMP_NAME4,

                            204 AS  LIST1_EMP_ID5,

                            NULL AS LIST1_EMP_NAME5

                            FROM dual

                            ),

                            table2 as (

                            SELECT

                            777 AS DEPT_NO,

                            103 AS  LIST1_EMP_ID1,

                            'Sid' AS LIST1_EMP_NAME1,

                            105 AS  LIST1_EMP_ID2,

                            'Ann' AS LIST1_EMP_NAME2,

                            100 AS  LIST1_EMP_ID3,

                            'Saj' AS LIST1_EMP_NAME3,

                            NULL AS  LIST1_EMP_ID4,

                            'Lee' AS LIST1_EMP_NAME4,

                            103 AS  LIST1_EMP_ID5,

                            'SAM' AS LIST1_EMP_NAME5

                            FROM dual

                            UNION ALL

                            SELECT

                            888 AS DEPT_NO,

                            400 AS  LIST1_EMP_ID1,

                            'Ral' AS LIST1_EMP_NAME1,

                            200 AS  LIST1_EMP_ID2,

                            'Betty' AS LIST1_EMP_NAME2,

                            500 AS  LIST1_EMP_ID3,

                            'Jacob' AS LIST1_EMP_NAME3,

                            NULL AS  LIST1_EMP_ID4,

                            NULL AS LIST1_EMP_NAME4,

                            NULL AS  LIST1_EMP_ID5,

                            NULL AS LIST1_EMP_NAME5

                            FROM dual

                            ),

                            matching as

                            (select nvl(one.dept_no,two.dept_no) dept_no,

                                    one.item item_one,

                                    one.id id_one,

                                    one.name name_one,

                                    two.item item_two,

                                    two.id id_two,

                                    two.name name_two,

                                    case when one.id = two.id

                                         then 'match'

                                         else 'NO match'

                                    end id_match,

                                    case when one.name = two.name

                                         then 'match'

                                         else 'NO match'

                                    end name_match,

                                    row_number() over (order by null) rn

                               from (select dept_no,item,id,name

                                       from (select *

                                               from table1

                                            )

                                     unpivot ((id,name) for item in ((LIST1_EMP_ID1,LIST1_EMP_NAME1) as 1,

                                                                         (LIST1_EMP_ID2,LIST1_EMP_NAME2) as 2,

                                                                         (LIST1_EMP_ID3,LIST1_EMP_NAME3) as 3,

                                                                         (LIST1_EMP_ID4,LIST1_EMP_NAME4) as 4,

                                                                         (LIST1_EMP_ID5,LIST1_EMP_NAME5) as 5

                                                                        )

                                             )

                                    ) one

                                    full outer join

                                    (select dept_no,item,id,name

                                       from (select *

                                               from table2

                                            )

                                     unpivot ((id,name) for item in ((LIST1_EMP_ID1,LIST1_EMP_NAME1) as 1,

                                                                         (LIST1_EMP_ID2,LIST1_EMP_NAME2) as 2,

                                                                         (LIST1_EMP_ID3,LIST1_EMP_NAME3) as 3,

                                                                         (LIST1_EMP_ID4,LIST1_EMP_NAME4) as 4,

                                                                         (LIST1_EMP_ID5,LIST1_EMP_NAME5) as 5

                                                                        )

                                             )

                                    ) two

                                 on one.dept_no = two.dept_no

                                and (one.id = two.id or one.name= two.name)

                            ),

                            step_1 as

                            (select dept_no,table_name,item,to_char(id) id,match_id,name,match_name,rn

                               from (select *

                                       from matching

                                    )

                            unpivot ((item,id,name,match_id,match_name) for table_name in ((item_one,id_one,name_one,id_match,name_match) as 'table1',

                                                                                            (item_two,id_two,name_two,id_match,name_match) as 'table2'

                                                                                           )

                                     )

                            ),

                            step_2 as

                            (select dept_no,table_name,nvl2(item,'list1_emp_'||x||to_char(item),null) item,id,match,x,rn   from (select *

                                       from step_1

                                    )

                            unpivot ((id,match) for x in ((id,match_id) as 'id',

                                                           (name,match_name) as 'name'

                                                          )

                                     )

                            )

                            select * from step_2 order by dept_no,rn,x

                             

                            DEPT_NOTABLE_NAMEITEMIDMATCHXRN
                            777table1list1_emp_id4103matchid1
                            777table2list1_emp_id1103matchid1
                            777table1list1_emp_name4Sidmatchname1
                            777table2list1_emp_name1Sidmatchname1
                            777table1list1_emp_id5105matchid2
                            777table2list1_emp_id2105matchid2
                            777table1list1_emp_name5Annmatchname2
                            777table2list1_emp_name2Annmatchname2
                            777table1list1_emp_id1100matchid3
                            777table2list1_emp_id3100matchid3
                            777table2list1_emp_name3SajNO matchname3
                            777table1list1_emp_name1RajNO matchname3
                            777table2list1_emp_id4-NO matchid4
                            777table1list1_emp_id2101NO matchid4
                            777table1list1_emp_name2Leematchname4
                            777table2list1_emp_name4Leematchname4
                            777table1list1_emp_id4103matchid5
                            777table2list1_emp_id5103matchid5
                            777table1list1_emp_name4SidNO matchname5
                            777table2list1_emp_name5SAMNO matchname5
                            888table1--NO matchid6
                            888table2list1_emp_id1400NO matchid6
                            888table1--NO matchname6
                            888table2list1_emp_name1RalNO matchname6
                            888table1list1_emp_id1200matchid7
                            888table2list1_emp_id2200matchid7
                            888table1list1_emp_name1Bettymatchname7
                            888table2list1_emp_name2Bettymatchname7
                            888table1list1_emp_id3201NO matchid8
                            888table2list1_emp_id3500NO matchid8
                            888table2list1_emp_name3Jacobmatchname8
                            888table1list1_emp_name3Jacobmatchname8
                            888table1list1_emp_id5204NO matchid9
                            888table2--NO matchid9
                            888table1list1_emp_name5-NO matchname9
                            888table2--NO matchname9
                            888table1list1_emp_id4-NO matchid10
                            888table2--NO matchid10
                            888table1list1_emp_name4JohnNO matchname10
                            888table2--NO matchname10
                            888table1list1_emp_id2202NO matchid11
                            888table2--NO matchid11
                            888table1list1_emp_name2JamesNO matchname11
                            888table2--NO matchname11


                            Regards

                             

                            Etbin

                            1 2 Previous Next