Joining tables and LEFT JOIN as new Columns in tsql

Question

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

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

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.

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

Like this.

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.

Answer 1

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);