/ * - Originally: http://community.9cbs.net/expert/topic/3841/3841808.xml? Temp = .4308588 - * /
- Test Data Create Table TB (Year Int, Month Int, No Varchar (10), Name Varchar (10), Department VARCHAR (10), Wages INT) INSERT TB SELECT 2004, 10, 'A001', 'AAA', 'DDD', 1000UNION All SELECT 2004, 10, 'B001', 'BBB', 'DDD', 800UNION All SELECT 2004, 11, 'A001', 'AAA', 'DDD', 1100UNION All SELECT 2004, 11, ' B001 ',' BBB ',' DDD ', 1000Union All Slect 2004, 12,' A001 ',' AAA ',' DDD ', 1200UNION All Slect 2004, 12,' B001 ',' BBB ',' DDD ', 1050
/ * - Treatment requirements
The month is a field, and the increase in the monthly salary increase, such as the above data requirements, and pay attention to the smallest annual month.
NO Name Department 2004_10 2004_11 2004_11 Inplification 2004_12 2004_12 Inplification ------ ------ ------------- ------------------------ A001 AAA DDD 1000 1100 10.00% 1200 9.09% B001 BBB DDD 800 1000 25.00% 1050 5.00% - * /
Go
- Query declare @s nvarchar (4000), @ i Intselect @s = '', @ i = 0select @ s = @ S ',' quotename (fd) '= SUM (Case When a.year =' Year 'and A.MONTH =' MONTH 'TEN A. salary end)' case @i when 0 Then '' Else ',' quotename (fd 'increase') '= CAST (SUM (CASE WHEN A.YEAR = ' Year ' and A.month = ' MONTH ' TEN A. Wage-B. Wage End) * 100. ' ' / Sum (Case When a.year = ' Year Year 'and A.MONTH =' MONTH 'TEN B. Wages End)' 'As Decimal (10, 2)) AS VARCHAR) ' '' 'end, @ i = @ i 1from (SELECT Year = RTRIM (YEAR), MONTH = RTRIM (MONTH), FD = RTRIM (YEAR) '_' RTRIM (MONTH) from TB Group by Year, Month) A Exec ('SELECT A.NO, A.NAME, a. Department ' @ s ' from TB a left join tb b on a.no = B.NO and A.Name = B.Name and a. Department = b. department and a.year = B.Year (B. Month / 12 and A.Month = B.MONTH% 12 1 Group by a.no, a.name, a. department ') GO - Delete Test DROP TABLE TB
