This is a horrible query. It is not flexible. If I have a SUP4 tomorrow, I'll have to edit it.
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
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 1 xyz 1 1000 2 2000 3 3000
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.
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
Edited by: Frank Kulash on Dec 3, 2010 3:01 PM
/* 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. */