Hello,
Please help me to query the data and make the format for report, the data in the table Prod_Sales is like this:
Customer_Name | Address | City | State | Zip | ProductName | ProductType | Month_Year | Month_Total |
---|---|---|---|---|---|---|---|---|
CustomerA | 100MainSt | NewYork | NY | 10006 | Table | T11 | Jan-18 | 100 |
CustomerA | 100MainSt | NewYork | NY | 10006 | Chair | C11 | Feb-18 | 200 |
CustomerA | 100MainSt | NewYork | NY | 10006 | Table | T11 | Apr-18 | 300 |
CustomerB | 200ChurchSt | LA | CA | 98765 | Board | B11 | May-18 | 100 |
CustomerB | 200ChurchSt | LA | CA | 98754 | Board | B11 | Jul-18 | 200 |
CustomerC | 300FarmRd | York | NJ | 771 | Chair | C11 | May-18 | 500 |
The output should display all customer info with each month amount horizontally and the YTD_Total at the end. Also, display monthly total for all product on each month vertically. The output should be like this:
Customer | Address | City | State | Zip | ProductName | ProductType | Jan | Feb | Mar | Apr | May | June | … | YTD_Total |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
CustomerA | 100MainSt | NewYork | NY | 10006 | Table | T11 | 100 | 300 | 400 | |||||
CustomerA | 100MainSt | NewYork | NY | 10006 | Chair | C11 | 200 | 200 | ||||||
CustomerB | 200ChurchSt | LA | CA | 98765 | Board | B11 | 100 | 200 | 300 | |||||
CustomerC | 300CentralRd | JerseyCity | NJ | 771 | Chair | C11 | 500 | 500 | ||||||
Total_Monthly_Sold | 100 | 200 | 900 | 200 | 1400 |
Really appreciate for any helps.
Thanks,
Lw1990