This discussion is archived
2 Replies Latest reply: Jun 9, 2009 9:33 AM by 691976 RSS

Report count and sum from many rows into many columns

691976 Newbie
Currently Being Moderated
I need to basically transpose many rows into counts and sums and display into one row with many columns.

Here's the example:
QUOTE_NAME CREATE_DATE PRICE SUPPLIER_NAME
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

becomes:
QUOTE_NAME SUP1 SUM(SUP1) SUP2 SUM(SUP2) SUP3 SUM(SUP3)
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 Guru
    Currently Being Moderated
    Hi,

    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
    http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402
    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
    @@dynamic_pivot_subscript
    FROM     scott.emp
    GROUP BY     deptno
    ORDER BY     deptno
    ;
    
    SPOOL     OFF
    
    --     *****  End of dynamic_pivot.sql  *****
    
    /*
    EXPLANATION:
    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
    {message:id=9979225}
    {message:id=1296899}
  • 2. Re: Report count and sum from many rows into many columns
    691976 Newbie
    Currently Being Moderated
    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!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points