PIVOT columns in SQL Server with their SUM

Question

In reference to my previous Question . With these changes in the tables : (*) are used to mark the changes in the tables.

Products Table

prod_ID   - int
prod_Name - varchar(100)
prod_Code - varchar(100)
*prod_Price- float

Orders Table

ord_ID  - int
ord_Qty - int
prod_ID - int
cus_ID  - float
*ord_PurchaseType - varchar(100)

Given that all items are priced as 10 dollars each. I want to make the final report to be something like this.

enter image description here

Currently, I'm using something like this: SUM(CASE WHEN ord_PurchaseType = 'cash' THEN prod_Price ELSE 0 END) to get the data, It works when I run in not inside the SET @query = N' ... query. Aside from that It is having an error saying that it has an invalid column named cash. I also tried putting the word cash in a variable but it still produces the same error.

@HJK i have sample datas from [Here](http://stackoverflow.com/questions/30912571/joining-tables-and-left-join-as-new-columns-in-tsql/30913094?noredirect=1#comment49981685_30913094) — response.write, Jun 22 '15 at 09:31
i din see any cash data from your example given. do you have cash at your product table? — HJK, Jun 22 '15 at 09:48

score 2 · Accepted Answer · answered Jun 22 '15 at 09:55

Okay this one was a bit more challenging but building on our last answer. The difference with what you want to do here is essentially aggregate 2 different values into the same report. Summing the product quantities and also summing the cash payments. You can't do it all in a single pivot statement, but you can do them separately and then join them together.

See SQL Fiddle

So here you see our original Pivot at the top which has been wrapped in a Common Table Expression (CTE) called ProductQuantities. We have then added a new CTE for the payments which you will notice has a slightly different Group By as we are only interested in the Cash Payments by Customer. Finally we join the results of both queries together on the customer to get our final result set.

SET @query = N'WITH ProductQuantities As (
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),

CustomerPayments As (
Select cus_Name, SUM(Case When ord_PurchaseType = ''cash'' then p.prod_Price * o.[ord_Qty] else 0 End) as [Cash Payments]
     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
)

Select pq.*, cp.[Cash Payments]
FROM ProductQuantities pq
INNER JOIN CustomerPayments cp on pq.cus_Name = cp.cus_Name'

thanks @sarin , I managed to create most of my reports with this. — response.write, Jun 23 '15 at 08:26
it's been so long, but is there a way to use your query into a view? — response.write, Sep 17 '15 at 10:57
Unfortunately you can't use dynamic sql in a view or UDF, unless things have changed recently. — sarin, Sep 17 '15 at 13:26
i'm having trouble with this code now. it occurs when the query returns many rows and/or columns. does this have a limit on the rows/columns it can return? thanks in advance. — response.write, Sep 18 '15 at 07:14
Check the data type you have declared @sql. Try varchar(max) if it's anything else. — sarin, Sep 18 '15 at 12:51

score -2 · Answer 2 · edited May 23 '17 at 10:26

-2

Try this one .

SUM(CASE WHEN ord_PurchaseType = ''cash'' ....

How do I escape a single quote in SQL Server?

edited May 23 '17 at 10:26

Community

1
1

answered Jun 22 '15 at 09:17

S.K

165
1
13

PIVOT columns in SQL Server with their SUM

2 Answers2

Linked