Manipulating SQL Query Results, How to Transform Data Similarly to Excel Pivot Table

Hi Experts,

Need some help with SQL and possible Excel and/or a VB script.

I have a query that returns data with the following structure, where the first row icontains my column headers:

field1,field2,field3,field4,field5,field6,NEW,field7,field8,field9,field10,PRODUCT_PRICE
order1,order_date,order_total,p_type,cust_tag1,cust_tag2,new,p_id,p_tag,p_cat,qty,35
order2,order_date,order_total,p_type,cust_tag1,cust_tag2,new,p_id,p_tag,p_cat,qty,5
order3,order_date,order_total,p_type,cust_tag1,cust_tag2,new,p_id,p_tag,p_cat,qty,15
order4,order_date,order_total,p_type,cust_tag1,cust_tag2,new,p_id,p_tag,p_cat,qty,15
order5,order_date,order_total,p_type,cust_tag1,cust_tag2,new,p_id,p_tag,p_cat,qty,7
order6,order_date,order_total,p_type,cust_tag1,cust_tag2,new,p_id,p_tag,p_cat,qty,8
order7,order_date,order_total,p_type,cust_tag1,cust_tag2,new,p_id,p_tag,p_cat,qty,15
order8,order_date,order_total,p_type,cust_tag1,cust_tag2,new,p_id,p_tag,p_cat,qty,5
order9,order_date,order_total,p_type,cust_tag1,cust_tag2,new,p_id,p_tag,p_cat,qty,15
order10,order_date,order_total,p_type,cust_tag1,cust_tag2,old,p_id,p_tag,p_cat,qty,11
order11,order_date,order_total,p_type,cust_tag1,cust_tag2,old,p_id,p_tag,p_cat,qty,9
order12,order_date,order_total,p_type,cust_tag1,cust_tag2,old,p_id,p_tag,p_cat,qty,25
order13,order_date,order_total,p_type,cust_tag1,cust_tag2,old,p_id,p_tag,p_cat,qty,10
order14,order_date,order_total,p_type,cust_tag1,cust_tag2,old,p_id,p_tag,p_cat,qty,45
order15,order_date,order_total,p_type,cust_tag1,cust_tag2,old,p_id,p_tag,p_cat,qty,14
order16,order_date,order_total,p_type,cust_tag1,cust_tag2,old,p_id,p_tag,p_cat,qty,5
order17,order_date,order_total,p_type,cust_tag1,cust_tag2,old,p_id,p_tag,p_cat,qty,10
order18,order_date,order_total,p_type,cust_tag1,cust_tag2,old,p_id,p_tag,p_cat,qty,26

I need to manipulate this data to return a table with this structure:

Price Tier,new,old,% New to Total
5,2,0,100%
10,2,1,66%
15,4,3,57%
25,0,2,0
35,1,1,50%
45,0,1,0%

where Price Tier represents all purchases at that price point or the preceding price point;
and new is a count of all new purchases between that price tier and the preceding price tier;
and old is a count of all old purchases between that price tier and the preceding price tier;
and %New to Total is the ratio of new purchases in that price tier to all purchase (new + old) in that price tier.

I have years of data structured this way by month on individual worksheets in one Excel file.  I would like to either manipulate the results of my existing query to return the summed results as 2nd example above before I dump the data into Excel.

OR be able to run a script on all the worksheets that will return a table with the data as above.

Either way, just need a quick solution rather than using pivot tables and copy/pasting formulas by hand.

An example sheet is attached with what I am trying to get to, step-by-step.

Start Free Trial