2 Replies Latest reply on Nov 29, 2015 10:58 PM by gaverill

    SQL Query2

    2836031

      Hi

       

      i/p table::
      eid fname lname
      1    raj       kumar
      2.   raj       kumar
      3    sai      kiran
      4    raj      kiran
      5    sai     kumar

      o/p table::::
      eid name   indicator     no.of times
      1    raj       first name   3
      2    sai      first name   2
      3    kumar last name   3
      4    kiran   last name   2

      Please help me with the above query.

      Thanks in advance....

        • 1. Re: SQL Query2
          Jagadekara
          eid name   indicator no.of times
          1raj   first name   3
          2sai  first name   2
          3kumar last name   3
          4kiran   last name   2

           

           

          That's the answer.

           

          How you can think people will give answers to such incomplete questions?

           

          Re: 2. How do I ask a question on the forums?

          • 2. Re: SQL Query2
            gaverill
            with    "i/p table"
                (
                    eid,  fname,    lname
                )
            as    (
                select    1,    'raj',    'kumar'
                from    DUAL union all
                select    2.,  'raj',    'kumar'
                from    DUAL union all
                select    3,    'sai',    'kiran'
                from    DUAL union all
                select    4,    'raj',    'kiran'
                from    DUAL union all
                select    5,    'sai',    'kumar'
                from    DUAL
                )
            ,    "intermediate table"
                (
                    name,    indicator,    "no.of times"
                )
            as    (
                select    fname,    'first name', count(*)
                from    "i/p table"
                group by
                    fname
                union all
                select    lname,    'last name',  count(*)
                from    "i/p table"
                group by
                    lname
                )
            ,    "o/p table"
                (
                    "eid", "name", "indicator", "no.of times"
                )
            as    (
                select    row_number() over (order by
                        indicator,
                        case indicator when 'first name' then name end asc,
                        case indicator when 'last name'  then name end desc
                    )
                ,    o.*
                from    "intermediate table" o
                )
            select    *
            from    "o/p table"
            order by
                "eid";
            

             

               eid  name   indicator  no.of times

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

                 1  raj        first name       3
                 2  sai       first name       2
                 3  kumar  last name        3
                 4  kiran    last name        2

             

            4 rows selected.

             

            I couldn't find any reasons, though, to use object-relational features here...

             

            Gerard