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
--> 测试数据: #Aif object_id('tempdb.dbo.#A') is not null drop table #Acreate table #A (名称 varchar(4),门店 varchar(3),数量 int)insert into #Aselect 'aaa1','aaa',10 union allselect 'bbb1','bbb',12 union allselect 'aaa1','bbb',4 union allselect 'bbb1','aaa',6 union allselect 'ccc1','aaa',2 union allselect 'ccc1','bbb',1 union allselect 'ccc1','aaa',1 union allselect 'aaa1','aaa',1 union allselect 'bbb1','bbb',1 union allselect 'ccc1','bbb',1--> SQL 2005select * from (select * from #A) as apivot (sum(数量) for 名称 in (aaa1,bbb1,ccc1)) as b/*门店 aaa1 bbb1 ccc1---- ----------- ----------- -----------aaa 11 6 3bbb 4 13 2*/