A data table in the field of years, months, days, amounts, payment methods and other fields, and now want to write a SQL statement, each day of the payment method amount (The payment method has multiple) row in the same line,
Finally, add a column subtotal to the amount of all the current payment methods. Such as:
The result of the original SQL query is this:
-------------------------------------------------------------------------------------------
And then the SQL results that you want to implement are finally rendered like this:
This is difficult for me, simple additions and deletions to check the left and right link SQL statements I will also write, this is a little more complicated I do not know how to do. How to group and then add rows to columns?
To find the degree of Niang Search, do not know how to describe their own to search the key words. Finally found a SQL master classmate to help solve the problem,
Others just a simple SQL statement to my needs to achieve, it is really let me admire! This pivot key is what east, I also first saw, never used, so powerful!
SQL statements:
SELECT Year, Months,tdays,[711],[Bankin],[Ituneshk],[711]+[Bankin]+[Ituneshk] as Total from(Select * from#Temp) asTpivot (SUM(amount) for [PayType] inch([711],[Bankin],[Ituneshk])) asT
The effect is as follows:
:
Original link: http://www.cnblogs.com/tandaxia/p/4888623.html
Complex SQL group query (pivot)