在表中加入1个字段DD,数字型,值一样比如为1
查询4:
transform max(name)
SELECT dd from ttg group by dd
pivot name
查询5:
transform sum(val(right(kaoshi,1))* pingshi)
SELECT dd from ttg group by dd
pivot name
SELECT *
FROM 查询4 union all SELECT *
FROM 查询5;
SELECT name,sum(val(right(kaoshi,1))* pingshi) from ttg group by name
这样得到的结果是
name Expr1001
李四 875
张三 700
还要转置一下
OR
用代码生成
SELECT sum(iif(name='张三',val(right(kaoshi,1))* pingshi ,0)) as 张三,sum(iif(name='李四',val(right(kaoshi,1))* pingshi ,0)) as 李四 from ttg
其中的张三、李四
select max(iif(name='张三',name,'')) as 张三,max(iif(name='李四',name,'')) as 李四 from ttg union all
SELECT sum(iif(name='张三',val(right(kaoshi,1))* pingshi ,0)) as 张三,sum(iif(name='李四',val(right(kaoshi,1))* pingshi ,0)) as 李四 from ttg