1

In this sample I have two tables containing data for my Customer and Products that a office supply shop has. which is seen below:

enter image description here enter image description here

The Above tables primarily functions to supply the required data for the third table which is the Orders table

enter image description here

Using the orders table, I can get a quick summary for reports that I need from customer and products table. The problem starts when I want to see the purchase count of each customer per item.

Like these tables for example.

enter image description here

Using the tables above, I need to create a report that shows the purchase count of each customer per item.

Like this.

enter image description here

I have used CTE to show items by Row_number but the code I have now is quite too long, I'm currently testing some way to do this the most efficient and most concise way. If someone can teach me how to do this the shorter way that would be great.

I'm not quite sure if my question's title is correct, I'll change it if it seems incorrect.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
response.write
  • 175
  • 5
  • 23
  • 4
    it's better to post data that can be copy and pasted rather than images, people can then try to replicate it without manually typing out your data. Better still, write a script that will populate a temp table with your sample data and show the issue with your desired output. – Tanner Jun 18 '15 at 10:21

1 Answers1

3

So Step one would be to start off with your base query. This gives you the final report information but in the wrong format. All that is needed now is to pivot it.

Select cus_Name, prod_Name, SUM(ord_Qty)
from Orders o
inner join Customers c on c.cus_ID = o.cus_ID
inner join Products p on p.prod_ID = o.Prod_ID
GROUP BY cus_Name, prod_Name

UPDATED

Pivoting this is more interesting as presumably the number of Products is not fixed. You therefore need to write a dynamic pivot to determine the columns based on the products ! Please note I haven't tested the below, but you should get the idea. Please refer to the plethora of posts on dynamic pivots for further questions on Pivots:

See SQLFiddle

DECLARE @colsForSelect VARCHAR(2000)
DECLARE @colsForPivot VARCHAR(2000)
DECLARE @query VARCHAR(4000)

SELECT  @colsForSelect = ISNULL(@colsForSelect + ',','') + ' ISNULL([' + prod_Name + '],0) AS [' + prod_Name + ']',
        @colsForPivot = ISNULL(@colsForPivot + ',','') + ' [' + prod_Name + ']'
FROM    Products

SET @query = N'SELECT cus_Name,'+ @colsForSelect +' 
FROM (    
     Select cus_Name, prod_Name, SUM(ord_Qty) as sum_ord_Qty
     from Orders o
     inner join Customers c on c.cus_ID = o.cus_ID
     inner join Products p on p.prod_ID = o.Prod_ID
     GROUP BY cus_Name, prod_Name
) p 
PIVOT (MAX([sum_ord_Qty]) FOR prod_Name IN ( '+ @colsForPivot +' )) 
AS pvt;'

EXECUTE(@query);
Community
  • 1
  • 1
sarin
  • 5,227
  • 3
  • 34
  • 63
  • I get the gist of what I should do with your answer but, I don't get the desired output using the code. The code outputs the products that are involved in the transaction in a single column, which is a little bit off for my desired output. – response.write Jun 19 '15 at 06:44
  • 1
    @chloe. It will be the pivot. Have a read how the pivot works. I will try to update my answer in the next few hours. – sarin Jun 19 '15 at 06:47
  • @Chloe I have updated the answer to include a SQLFiddle example. This is now working as expected\asked. – sarin Jun 19 '15 at 08:50
  • I'm trying to use a `WHERE` clause in the query now but i can't seem to make it work. Can you help me again how to do it? or I'll make another question for that. Thanks a lot. – response.write Jun 22 '15 at 06:07
  • Add the WHERE clause after the last INNER JOIN and before the GROUP BY. Can you can explain in more detail what you are trying to do ? – sarin Jun 22 '15 at 08:06
  • In the Final Report, I'm tying to add another 2 more columns which will SUM the price multiplied by ordered quantity of items. which depends if the items was purchased by cash or check. I'm currently using `SUM(CASE WHEN ... ` for this condition. I added the new column _ord_PurchaseType_ in the Orders Table which is inputted by either **cash** or **check** , column `prod_Price` is also added in the Products table – response.write Jun 22 '15 at 08:25
  • Bit difficult to do this in the comments. Best make another question like you did above and give you expected answer and what you have tried – sarin Jun 22 '15 at 08:37
  • Ok, I'll do that, I'll comment the link later. – response.write Jun 22 '15 at 08:38
  • I just posted the question [Here](http://stackoverflow.com/questions/30975910/pivot-columns-in-sql-server-with-their-sum) @sarin – response.write Jun 22 '15 at 09:04