11 Replies Latest reply on Feb 12, 2019 12:37 PM by Solomon Yakobson

    Column to row transpose

    anand_gp

      Hi,

       

      I was looking for generating something like -

       

       

      And my data is like -

       

      SELECT TO_CHAR(A.COLLECTION_DATE,'DD-MON-YY') COL_DATE, B.AGENT_NAME, SUM(A.AMOUNT) AMOUNT
      FROM   DAILY_COLLECTION A
      INNER  JOIN DAILY_COLLECTION_AGENTS B USING (AGENT_CODE)
      GROUP  BY A.COLLECTION_DATE, B.AGENT_NAME
      ORDER  BY A.COLLECTION_DATE;
      

       

      COL_DATEAGENT_NAMEAMOUNT
      31-JAN-19PETER18000
      31-JAN-19RAVI48500
      01-FEB-19RAVI13000
      02-FEB-19PETER6000
      02-FEB-19RAVI3000

       

      I can achieve this using below query.  But problem is, AGENT_NAME is hardcoded.  Tomorrow, if a new agent introduced, I need to change the script. Number of Agents will be < 5.  Over the year, there may be 1 or 2 increase.

       

      SELECT TO_CHAR(COLLECTION_DATE,'DD-MON-YY') COL_DT,  SUM(PETER) PETER, SUM(RAVI) RAVI
      FROM   (
        SELECT COLLECTION_DATE,
          CASE WHEN AGENT_NAME = 'PETER' THEN AMOUNT END PETER,
          CASE WHEN AGENT_NAME = 'RAVI'  THEN AMOUNT END RAVI
        FROM   DAILY_COLLECTION
        )
      GROUP  BY COLLECTION_DATE
      ORDER  BY COLLECTION_DATE;
      

       

      Please help me if there is any other way to achieve this.  Thanks.

       

      CREATE TABLE DAILY_COLLECTION (COLLECTION_DATE DATE, AGENT_NAME VARCHAR2(10), AMOUNT NUMBER);
      INSERT INTO DAILY_COLLECTION VALUES ( TO_DATE('01/31/2019','MM-DD-YYYY'), 'RAVI', 2500);
      INSERT INTO DAILY_COLLECTION VALUES ( TO_DATE('01/31/2019','MM-DD-YYYY'), 'PETER', 2000);
      INSERT INTO DAILY_COLLECTION VALUES ( TO_DATE('02/02/2019','MM-DD-YYYY'), 'RAVI', 3000);
      INSERT INTO DAILY_COLLECTION VALUES ( TO_DATE('01/31/2019','MM-DD-YYYY'), 'RAVI', 5000);
      INSERT INTO DAILY_COLLECTION VALUES ( TO_DATE('02/01/2019','MM-DD-YYYY'), 'RAVI', 5000);
      INSERT INTO DAILY_COLLECTION VALUES ( TO_DATE('01/31/2019','MM-DD-YYYY'), 'RAVI', 6000);
      INSERT INTO DAILY_COLLECTION VALUES ( TO_DATE('01/31/2019','MM-DD-YYYY'), 'PETER', 7000);
      INSERT INTO DAILY_COLLECTION VALUES ( TO_DATE('01/31/2019','MM-DD-YYYY'), 'RAVI', 9000);
      INSERT INTO DAILY_COLLECTION VALUES ( TO_DATE('02/01/2019','MM-DD-YYYY'), 'RAVI', 8000);
      INSERT INTO DAILY_COLLECTION VALUES ( TO_DATE('01/31/2019','MM-DD-YYYY'), 'PETER', 9000);
      INSERT INTO DAILY_COLLECTION VALUES ( TO_DATE('02/02/2019','MM-DD-YYYY'), 'PETER', 6000);
      INSERT INTO DAILY_COLLECTION VALUES ( TO_DATE('01/31/2019','MM-DD-YYYY'), 'RAVI', 26000);
      

       

      Best Regards,

      -Anand

        • 3. Re: Column to row transpose
          Solomon Yakobson

          And what would you do with such query if you don't know number of columns it returns? Use it as a report? Then use reporting tools - every reporting tool can do that. Other than that - use SQL*Plus to generate SQL and then run it using SQL*Plus substitution variables:

           

          COLUMN AGENT_NAME_LIST NEW_VALUE AGENT_NAME_LIST NOPRINT

          COLUMN AGENT_COLUMN_LIST NEW_VALUE AGENT_COLUMN_LIST NOPRINT

          WITH T AS (

                     SELECT  XMLAGG(

                                    XMLELEMENT(

                                               E,

                                               '''' || AGENT_NAME || ''''

                                              )

                                   ) AGENT_NAME_LIST,

                             XMLAGG(

                                    XMLELEMENT(

                                               E,

                                               '"''' || AGENT_NAME || '''"'

                                              )

                                   ) AGENT_COLUMN_LIST

                       FROM  DAILY_COLLECTION

                    )

          SELECT  XMLCAST(

                          XMLQUERY(

                                   'string-join(distinct-values(//text()),",")'

                                   PASSING AGENT_NAME_LIST

                                   RETURNING CONTENT

                                  )

                          AS CLOB

                         ) AGENT_NAME_LIST,

                  XMLCAST(

                          XMLQUERY(

                                   'string-join(distinct-values(//text()),",")'

                                   PASSING AGENT_COLUMN_LIST

                                   RETURNING CONTENT

                                  )

                          AS CLOB

                         ) AGENT_COLUMN_LIST

            FROM  T

          /

          SET VERIFY OFF

          SELECT  TO_CHAR(COLLECTION_DATE,'DD-MON-YY') COL_DT,

                  &AGENT_COLUMN_LIST

            FROM  DAILY_COLLECTION

            PIVOT(

                  SUM(AMOUNT)

                  FOR AGENT_NAME IN (&AGENT_NAME_LIST)

                 )

          /

           

          COL_DT                'PETER'     'RAVI'

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

          31-JAN-19               18000      48500

          02-FEB-19                6000       3000

          01-FEB-19                          13000

           

          SQL>

           

          Now I add an agent:

           

          SQL> INSERT INTO DAILY_COLLECTION VALUES ( TO_DATE('01/31/2019','MM-DD-YYYY'), 'anand_gp',9999);

           

          1 row created.

           

          SQL> WITH T AS (

            2             SELECT  XMLAGG(

            3                            XMLELEMENT(

            4                                       E,

            5                                       '''' || AGENT_NAME || ''''

            6                                      )

            7                           ) AGENT_NAME_LIST,

            8                     XMLAGG(

            9                            XMLELEMENT(

          10                                       E,

          11                                       '"''' || AGENT_NAME || '''"'

          12                                      )

          13                           ) AGENT_COLUMN_LIST

          14               FROM  DAILY_COLLECTION

          15            )

          16  SELECT  XMLCAST(

          17                  XMLQUERY(

          18                           'string-join(distinct-values(//text()),",")'

          19                           PASSING AGENT_NAME_LIST

          20                           RETURNING CONTENT

          21                          )

          22                  AS CLOB

          23                 ) AGENT_NAME_LIST,

          24          XMLCAST(

          25                  XMLQUERY(

          26                           'string-join(distinct-values(//text()),",")'

          27                           PASSING AGENT_COLUMN_LIST

          28                           RETURNING CONTENT

          29                          )

          30                  AS CLOB

          31                 ) AGENT_COLUMN_LIST

          32    FROM  T

          33  /

           

          SQL> SELECT  TO_CHAR(COLLECTION_DATE,'DD-MON-YY') COL_DT,

            2          &AGENT_COLUMN_LIST

            3    FROM  DAILY_COLLECTION

            4    PIVOT(

            5          SUM(AMOUNT)

            6          FOR AGENT_NAME IN (&AGENT_NAME_LIST)

            7         )

            8  /

           

          COL_DT                'PETER'     'RAVI' 'anand_gp'

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

          31-JAN-19               18000      48500       9999

          02-FEB-19                6000       3000

          01-FEB-19                          13000

           

          SQL>

           

          Other solutions are cursor bind variable, DBMS_SQL or ODCI Table Interface (has limitations).

           

          SY.

          • 4. Re: Column to row transpose
            Solomon Yakobson

            Actually, I missed number of agents is < 5. Then you can do something like:

             

            WITH T AS (

                      SELECT  D.*,

                              DENSE_RANK() OVER(ORDER BY AGENT_NAME) AGENT_ID

                        FROM  DAILY_COLLECTION D

                      )

            SELECT  TO_CHAR(COLLECTION_DATE,'DD-MON-YY') COL_DT,

                    AGENT1_NAME,

                    AGENT1_AMOUNT,

                    AGENT2_NAME,

                    AGENT2_AMOUNT,

                    AGENT3_NAME,

                    AGENT3_AMOUNT,

                    AGENT4_NAME,

                    AGENT4_AMOUNT,

                    AGENT5_NAME,

                    AGENT5_AMOUNT

              FROM  T

              PIVOT(

                    SUM(AMOUNT) AMOUNT,

                    MAX(AGENT_NAME) NAME

                    FOR AGENT_ID IN (1 AGENT1,2 AGENT2,3 AGENT3,4 AGENT4,5 AGENT5)

                  )

            /

             

            COL_DT            AGENT1_NAM AGENT1_AMOUNT AGENT2_NAM AGENT2_AMOUNT AGENT3_NAM AGENT3_AMOUNT AGENT4_NAM AGENT4_AMOUNT AGENT5_NAM AGENT5_AMOUNT

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

            31-JAN-19          PETER              18000 RAVI              48500

            02-FEB-19          PETER              6000 RAVI                3000

            01-FEB-19                                  RAVI              13000

             

            SQL>

             

            Now, when new agent is added:

             

            SQL> INSERT INTO DAILY_COLLECTION VALUES ( TO_DATE('01/31/2019','MM-DD-YYYY'), 'anand_gp',9999);

             

            1 row created.

             

            SQL> WITH T AS (

              2            SELECT  D.*,

              3                    DENSE_RANK() OVER(ORDER BY AGENT_NAME) AGENT_ID

              4              FROM  DAILY_COLLECTION D

              5            )

              6  SELECT  TO_CHAR(COLLECTION_DATE,'DD-MON-YY') COL_DT,

              7          AGENT1_NAME,

              8          AGENT1_AMOUNT,

              9          AGENT2_NAME,

            10          AGENT2_AMOUNT,

            11          AGENT3_NAME,

            12          AGENT3_AMOUNT,

            13          AGENT4_NAME,

            14          AGENT4_AMOUNT,

            15          AGENT5_NAME,

            16          AGENT5_AMOUNT

            17    FROM  T

            18    PIVOT(

            19          SUM(AMOUNT) AMOUNT,

            20          MAX(AGENT_NAME) NAME

            21          FOR AGENT_ID IN (1 AGENT1,2 AGENT2,3 AGENT3,4 AGENT4,5 AGENT5)

            22        )

            23  /

             

            COL_DT            AGENT1_NAM AGENT1_AMOUNT AGENT2_NAM AGENT2_AMOUNT AGENT3_NAM AGENT3_AMOUNT AGENT4_NAM AGENT4_AMOUNT AGENT5_NAM AGENT5_AMOUNT

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

            31-JAN-19          PETER              18000 RAVI              48500 anand_gp            9999

            02-FEB-19          PETER              6000 RAVI                3000

            01-FEB-19                                  RAVI              13000

             

            SQL>

             

            Obviously, you would need to ajust the above query if number of agents will exceed 5.

             

            SY.

            • 5. Re: Column to row transpose
              SalimWer

              Hi

              In SQLPLUS  you can simply use LISTAGG to generate the list of names as following

               

               

              COLUMN ALL_AGENT_NAMES NEW_VALUE ALL_AGENT_NAMES ;

              SELECT LISTAGG(''''||AGENT_NAME||'''', ', ') WITHIN GROUP (ORDER BY AGENT_NAME) ALL_AGENT_NAMES from (SELECT DISTINCT AGENT_NAME FROM DAILY_COLLECTION);

               

              SET VERIFY OFF

              SELECT *

              FROM

                   (SELECT COLLECTION_DATE, AGENT_NAME, AMOUNT

                    FROM DAILY_COLLECTION

                   ) tab1  PIVOT  (SUM(AMOUNT) for AGENT_NAME IN (&ALL_AGENT_NAMES) )

                           ;

               

               

              Regards

              Salim

              1 person found this helpful
              • 6. Re: Column to row transpose
                BluShadow

                SalimWer wrote:

                 

                Hi

                In SQLPLUS you can simply use LISTAGG to generate the list of names as following

                 

                 

                COLUMN ALL_AGENT_NAMES NEW_VALUE ALL_AGENT_NAMES ;

                SELECT LISTAGG(''''||AGENT_NAME||'''', ', ') WITHIN GROUP (ORDER BY AGENT_NAME) ALL_AGENT_NAMES from (SELECT DISTINCT AGENT_NAME FROM DAILY_COLLECTION);

                 

                SET VERIFY OFF

                SELECT *

                FROM

                (SELECT COLLECTION_DATE, AGENT_NAME, AMOUNT

                FROM DAILY_COLLECTION

                ) tab1 PIVOT (SUM(AMOUNT) for AGENT_NAME IN (&ALL_AGENT_NAMES) )

                ;

                 

                 

                Regards

                Salim

                 

                 

                List aggregation is not the same as pivoting the data in to separate columns, so it's not "simply" a case of doing aggregation.

                You're solution also depends on substitution variables within the SQL*Plus environment, so that's not going to work in other environments, such as PL/SQL code itself.

                • 7. Re: Column to row transpose
                  Solomon Yakobson

                  Yes, we can use LISTAGG here instead of XMLAGG I used (I overlooked number of agents is < 5). But keep in mind LISTAGG is not a generic solution since it returns VARCHAR2 and therefore is limited to 4000 bytes while pivoting FOR clause is limited to 1000 expressions in which case just quotes and commas LISTAGG will use  2999 bytes (first quote, 999 quote-comma-quote and last quote) leaving only 1001 bytes for agent names which leaves us with 1 character per agent name even when using single byte character code.

                   

                  SY.

                  • 8. Re: Column to row transpose
                    anand_gp

                    OP was -

                    I can achieve this using below query.  But problem is, AGENT_NAME is hardcoded.  Tomorrow, if a new agent introduced, I need to change the script. Number of Agents will be < 5.  Over the year, there may be 1 or 2 increase.

                     

                    Even if I use PIVOT, hard-coding is needed.  Is there a way to achieve this without hard-coding of Agent_name?  Tomorrow if a new agent is added, the code has to work without any modifications.

                     

                    Thanks.

                    • 9. Re: Column to row transpose
                      anand_gp

                      Hi Solomon,

                       

                      And what would you do with such query if you don't know number of columns it returns?

                       

                      Yes, you are right.  That answers my question !!  If I am using pl/sql script to generate output report, then I can use the PIVOT solution you mentioned.  However, if I am building a report in APEX for example, the whole concept is irrelevant.  While creating a report on APEX, we need to know column details in advance.  So, even though, there is a solution available at pl/sql level, it is of no use for APEX report, because, report template is already built.  Modification in report is still needed, for new columns to appear in that report.

                       

                      Even, I checked the thread No. 5 by Salim which is also in the similar lines. 

                      Thanks every one for your valuable inputs.

                       

                      Best Regards,

                      -Anand

                      • 10. Re: Column to row transpose
                        BluShadow

                        anand_gp wrote:

                         

                        Hi Solomon,

                         

                        And what would you do with such query if you don't know number of columns it returns?

                         

                        Yes, you are right. That answers my question !! If I am using pl/sql script to generate output report, then I can use the PIVOT solution you mentioned. However, if I am building a report in APEX for example, the whole concept is irrelevant. While creating a report on APEX, we need to know column details in advance. So, even though, there is a solution available at pl/sql level, it is of no use for APEX report, because, report template is already built. Modification in report is still needed, for new columns to appear in that report.

                         

                        Even, I checked the thread No. 5 by Salim which is also in the similar lines.

                        Thanks every one for your valuable inputs.

                         

                        Best Regards,

                        -Anand

                         

                         

                        However, if you created a view on your data which pivots it with known column names then you can use that in your Apex reports as you would build your apex form around those columns.  The form would also need an additional query to list (perhaps at the side) which column relates to which "agent", for the user to reference.

                        • 11. Re: Column to row transpose
                          Solomon Yakobson

                          anand_gp wrote:

                           

                          However, if I am building a report in APEX for example, the whole concept is irrelevant.

                          I don't have much APEX experience but I believe pivot data within interactive report is available starting APEX 5. Or you can simply write PL/SQL function that returns pivot SQL tailored to number of agents - same as my SQL*Plus example, just no need for XMLAGG (or LISTAGG) - simply loop through distinct agent names. Then call it in APEX to pass generated SQL to report.

                          Also, you might get a better answer from APEX forum.

                           

                          SY.