2 Replies Latest reply on Jun 9, 2009 4:33 PM by 691976

    Report count and sum from many rows into many columns

      I need to basically transpose many rows into counts and sums and display into one row with many columns.

      Here's the example:
      abc 24-APR-09 1004 SUP2
      abc 24-APR-09 680 SUP2
      abc 24-APR-09 170 SUP2
      abc 24-APR-09 200 SUP2
      abc 24-APR-09 200 SUP1
      abc 24-APR-09 692 SUP1
      abc 24-APR-09 692 SUP3
      abc 24-APR-09 3041.99 SUP3
      abc 24-APR-09 857 SUP1

      abc 3 1749 4 2054 2 3733.99

      And so far, the SQL:
      select distinct q1.quote_name as quote_name,
        (select count(*) from quote_table bc_i1 where bc_i1.quote_id= q1.quote_id and bc_i1.supplier_name='SUP1') as SUP1,
        (select count(*) from quote_table bc_i2 where bc_i2.quote_id= q1.quote_id and bc_i2.supplier_name='SUP2') as SUP2,
        (select count(*) from quote_table bc_i2 where bc_i2.quote_id= q1.quote_id and bc_i2.supplier_name='SUP3') as SUP3
      FROM quote_table q1
      This is a horrible query. It is not flexible. If I have a SUP4 tomorrow, I'll have to edit it.
      Second, it is slow
      Third, I am not including the sum of price

      I might have to break that down. Maybe even incorporate PL/SQL? Any help would be appreciated.

      PS: I'd like to format the tables for better readability... Can I do that?
        • 1. Re: Report count and sum from many rows into many columns
          Frank Kulash

          What you're doing is called a pivot.
          You're doing it with scalar sub-queries, which require one pass through the table for each column of output. It's more efficient to use CASE (or DECODE) expressions to get all the columns in one pass.

          As you realized, this assumes you know how many columns there will be. If you have 3 columns today, you can write a query that produces 10 columns: 7 will be blank today, but if you add a 4th column next week, you won't have to re-write the query. Of course, that merely postpones the day when you will have to re-write the query, and, in the meantime, the output has 7 ugly NULL columns.

          You can use dynamic SQL to write a query (or the variable parts of a query) for you. See the script at the end of this message for an example using SQL*Plus.

          Another approach is to hard-code a fixed number of columns, say 3, on a row. If you add a 4th column, the output will wrap to a second row.
          That is, with 3 columns you might get this output:
          1  abc   3   1749     4     2054       2     3733.99
          1  xyz   1   1000     2     2000       3     3000
          but when you add a 4th column, you start getting two rows of output per quote:
          1  abc   3   1749     4     2054       2     3733.99
          2  abc   1   1234
          1  xyz   1   1000     2     2000       3     3000
          2  xyz   4   4000
          Another approach is to use String Aggregation to combine all the counts and sums into one humongeous VARCHAR2 column, padded so that the numbers appear to be in different columns.
          The following pages
          show several different ways to do string aggregation.

          Example of dynamic pivot:
          How to Pivot a Table with a Dynamic Number of Columns
          This works in any version of Oracle
          The "SELECT ... PIVOT" feature introduced in Oracle 11
          is much better for producing XML output.
          Say you want to make a cross-tab output of
          the scott.emp table.
          Each row will represent a department.
          There will be a separate column for each job.
          Each cell will contain the number of employees in
               a specific department having a specific job.
          The exact same solution must work with any number
          of departments and columns.
          (Within reason: there's no guarantee this will work if you 
          want 2000 columns.)
          Case 0 "Basic Pivot" shows how you might hard-code three
          job types, which is exactly what you DON'T want to do.
          Case 1 "Dynamic Pivot" shows how get the right results
          dynamically, using SQL*Plus.  
          (This can be easily adapted to PL/SQL or other tools.)
          PROMPT     ==========  0. Basic Pivot  ==========
          SELECT     deptno
          ,     COUNT (CASE WHEN job = 'ANALYST' THEN 1 END)     AS analyst_cnt
          ,     COUNT (CASE WHEN job = 'CLERK'   THEN 1 END)     AS clerk_cnt
          ,     COUNT (CASE WHEN job = 'MANAGER' THEN 1 END)     AS manager_cnt
          FROM     scott.emp
          WHERE     job     IN ('ANALYST', 'CLERK', 'MANAGER')
          GROUP BY     deptno
          ORDER BY     deptno
          PROMPT     ==========  1. Dynamic Pivot  ==========
          --     *****  Start of dynamic_pivot.sql  *****
          -- Suppress SQL*Plus features that interfere with raw output
          SET     FEEDBACK     OFF
          SET     PAGESIZE     0
          SPOOL     p:\sql\cookbook\dynamic_pivot_subscript.sql
          SELECT     DISTINCT
               ',     COUNT (CASE WHEN job = '''
          ||     job
          ||     ''' '     AS txt1
          ,     'THEN 1 END)     AS '
          ||     job
          ||     '_CNT'     AS txt2
          FROM     scott.emp
          ORDER BY     txt1;
          SPOOL     OFF
          -- Restore SQL*Plus features suppressed earlier
          SET     FEEDBACK     ON
          SET     PAGESIZE     50
          SPOOL     p:\sql\cookbook\dynamic_pivot.lst
          SELECT     deptno
          FROM     scott.emp
          GROUP BY     deptno
          ORDER BY     deptno
          SPOOL     OFF
          --     *****  End of dynamic_pivot.sql  *****
          The basic pivot assumes you know the number of distinct jobs,
          and the name of each one.  If you do, then writing a pivot query
          is simply a matter of writing the correct number of ", COUNT ... AS ..."\
          lines, with the name entered in two places on each one.  That is easily
          done by a preliminary query, which uses SPOOL to write a sub-script 
          (called dynamic_pivot_subscript.sql in this example).
          The main script invokes this sub-script at the proper point.
          In practice, .SQL scripts usually contain one or more complete
          statements, but there's nothing that says they have to.
          This one contains just a fragment from the middle of a SELECT statement.
          Before creating the sub-script, turn off SQL*Plus features that are 
          designed to help humans read the output (such as headings and 
          feedback messages like "7 rows selected.", since we do not want these 
          to appear in the sub-script.
          Turn these features on again before running the main query.
          Edited by: Frank Kulash on Dec 3, 2010 3:01 PM
          Updated links.

          Edited by: Frank Kulash on Jun 13, 2011 6:27 PM
          Corrected spelling.

          Edited by: Frank Kulash on Apr 14, 2012 10:25 AM
          See the forum FAQ {message:id=9360005} for lineks to some PL/SQL solutions, including
          • 2. Re: Report count and sum from many rows into many columns
            Frank... I just started reading your reply and I want to thank you! Spot on! I have not read your entire reply yet (I WILL!), but I think you just gave me a huge boost!

            My boss does make all these in Excel, using pivots. So duh! I should have googled that, I guess. It makes much sense now.

            Thanks again!