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?