请教Sql sever中怎么才能实现Access中的交叉表查询

请问Sql sever中如何才能实现Access中的交叉表查询急
Access 中的代码如下:
TRANSFORM Sum(a.数量) AS 数量之总计
SELECT a.门店
FROM a
GROUP BY a.门店
PIVOT a.名称;

表A 内容 :
名称门店数量
aaa1 aaa 10
bbb1 bbb 12
aaa1 bbb 4
bbb1 aaa 6
ccc1 aaa 2
ccc1 bbb 1
ccc1 aaa 1
aaa1 aaa 1
bbb1 bbb 1
ccc1 bbb 1

查询完的结果为:
门店 aaa1 bbb1 ccc1
aaa 11 6 3
bbb 4 13 2

------解决方案--------------------

SQL code

--> 测试数据: #A
if object_id('tempdb.dbo.#A') is not null drop table #A
create table #A (名称 varchar(4),门店 varchar(3),数量 int)
insert into #A
select 'aaa1','aaa',10 union all
select 'bbb1','bbb',12 union all
select 'aaa1','bbb',4 union all
select 'bbb1','aaa',6 union all
select 'ccc1','aaa',2 union all
select 'ccc1','bbb',1 union all
select 'ccc1','aaa',1 union all
select 'aaa1','aaa',1 union all
select 'bbb1','bbb',1 union all
select 'ccc1','bbb',1

--> SQL 2005
select * from 
    (select * from #A) as a
pivot
    (sum(数量) for 名称 in (aaa1,bbb1,ccc1)) as b

/*
门店 aaa1        bbb1        ccc1
---- ----------- ----------- -----------
aaa  11          6           3
bbb  4           13          2
*/

请教Sql sever中怎么才能实现Access中的交叉表查询

相关推荐