一些实现特殊的结果的SQL语句

原来数据为
 1,'2',3,4,4,4
 1,'3',3,3,3,3
 1,'4',5,5,5,5
 2,'2',3,3,3,3
 2,'3',3,3,4,4
 2,'4',4,4,3,6
 3,'2',2,2,2,2
 3,'4',4,4,3,6
 3,'4',4,4,3,6
要得到为

1 2 3 4 4 4
1 3 3 3 3 3
1 4 5 5 5 5
1 合计 11 12 12 12
2 2 3 3 3 3
2 3 3 3 4 4
2 4 4 4 3 6
2 合计 10 10 10 13
3 2 2 2 2 2
3 4 8 8 6 12
3 合计 10 10 8 14

其语句为：创建动态表

declare @test table(areacode int,serverName varchar(20),a1 int,a2 int,a3 int,a4 int)
insert into @test
select 1,'2',3,4,4,4 union all
select 1,'3',3,3,3,3 union all
select 1,'4',5,5,5,5 union all
select 2,'2',3,3,3,3 union all
select 2,'3',3,3,4,4 union all
select 2,'4',4,4,3,6 union all
select 3,'2',2,2,2,2 union all
select 3,'4',4,4,3,6 union all
select 3,'4',4,4,3,6

select *
from (
select case when (grouping(areacode)=1) then '-1' else areacode end as 'areacode',
case when (grouping(serverName)=1) then '合计' else serverName end as 'serverName',
       sum(a1) as 'a1',
       sum(a2) as 'a2',
       sum(a3) as 'a3',
       sum(a4) as 'a4'
from @test
group by areacode,serverName
with rollup
) t
where areacode<>-1

交叉联结：
有数据为

Year      Quarter      Amount
----      -------      ------
1990      1           1.1
1990      2           1.2
1990      3           1.3
1990      4           1.4
1991      1           2.1
1991      2           2.2
1991      3           2.3
1991      4           2.4

要形成报表为：
Year   Q1   Q2    Q3     Q4
 
1990   1.1  1.2   1.3    1.4
 
1991   2.1  2.2   2.3    2.4

其语句为：

SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal
FROM (SELECT Year,
             SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1,
             SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2,
             SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3,
             SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4
     FROM Pivot AS P
     GROUP BY P.Year) AS P1