1 Reply Latest reply: Aug 26, 2014 3:31 PM by Frank Kulash RSS

    Pivot query help

    Subhash C-Oracle

      need help on building pivot query

       

      SELECT * FROM TEST1

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

      COMP_NAME          PERSON          VALUE

      COMP1                    ABC                     3

      COMP2                    ABC                     5

      COMP1                    CAD                     3

      COMP3                    CAD                     5

      COMP2                    TES                      1

      COMP1                    TES                      5

      COMP3                    ABC                      2

       

      myquery

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

      select null link, PERSON label, COUNT(VALUE) value1

      from "test1"

      group by PERSON ORDER BY PERSON

       

      Results

      ---------

      Link               label               value1

      -                     ABC                     3

      -                     CAD                     2

      -                     TES                      2

       

      My Requirement

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

      can we have output something like this using pivot concept? if yes can you share a sample query pls.


      Link               label               value1

      -                     ABC               COMP1,COMP2,COMP3

      -                     CAD               COMP2,COMP1

      -                     TES               COMP1,COMP3

        • 1. Re: Pivot query help
          Frank Kulash

          Hi,

          Subhash C-Oracle wrote:

           

          need help on building pivot query

           

          SELECT * FROM TEST1

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

          COMP_NAME          PERSON          VALUE

          COMP1                    ABC                    3

          COMP2                    ABC                    5

          COMP1                    CAD                    3

          COMP3                    CAD                    5

          COMP2                    TES                      1

          COMP1                    TES                      5

          COMP3                    ABC                      2

           

          myquery

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

          select null link, PERSON label, COUNT(VALUE) value1

          from "test1"

          group by PERSON ORDER BY PERSON

           

          Results

          ---------

          Link              label              value1

          -                    ABC                    3

          -                    CAD                    2

          -                    TES                      2

           

          My Requirement

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

          can we have output something like this using pivot concept? if yes can you share a sample query pls.


          Link              label              value1

          -                    ABC              COMP1,COMP2,COMP3

          -                    CAD              COMP2,COMP1

          -                    TES              COMP1,COMP3

          That sounds like a job for LISTAGG:

           

          SELECT    NULL                                    AS link

          ,        label

          ,        LISTAGG (comp_name, ',')

                        WITHIN GROUP (ORDER BY comp_name)  AS value1

          FROM      test1

          GROUP BY  label

          ;

          If you'd care to post CREATE TABLE and INSERT statements for your sample data, then I could test it.

           

          Are you sure the results you posted are what you want from the given data?

           

          Is the order of the items in a list significant?  That is, when you say you want the results:

          COMP2,COMP1

          would you be equally happy with

          COMP1,COMP2

          ?  If the order is significant, explain what that order is.