在SQL查询语句行转列横向显示中access中没有CASE,要用IIF代替
select iif(sex= '1 ', '男 ', '女 ') from tablename
select country, sum(case when type='A' then money end) as A,
sum(case when type='B' then money end) as B,
sum(case when type='C' then money end) as C
from table1
group by country
select b.MemberName,
count(b.MemberName),
SUM(case when CONVERT(varchar(7),a.CreateTime,120)='2016-01' then a.PayAmount end) as '2016-01',
SUM(case when CONVERT(varchar(7),a.CreateTime,120)='2016-02' then a.PayAmount end) as '2016-02',
SUM(case when CONVERT(varchar(7),a.CreateTime,120)='2016-03' then a.PayAmount end) as '2016-03',
SUM(case when CONVERT(varchar(7),a.CreateTime,120)='2016-04' then a.PayAmount end) as '2016-04',
SUM(case when CONVERT(varchar(7),a.CreateTime,120)='2016-05' then a.PayAmount end) as '2016-05',
SUM(case when CONVERT(varchar(7),a.CreateTime,120)='2016-06' then a.PayAmount end) as '2016-06',
SUM(case when CONVERT(varchar(7),a.CreateTime,120)='2016-07' then a.PayAmount end) as '2016-07',
SUM(case when CONVERT(varchar(7),a.CreateTime,120)='2016-08' then a.PayAmount end) as '2016-08',
SUM(case when CONVERT(varchar(7),a.CreateTime,120)='2016-09' then a.PayAmount end) as '2016-09',
SUM(case when CONVERT(varchar(7),a.CreateTime,120)='2016-10' then a.PayAmount end) as '2016-10',
SUM(case when CONVERT(varchar(7),a.CreateTime,120)='2016-11' then a.PayAmount end) as '2016-11',
SUM(case when CONVERT(varchar(7),a.CreateTime,120)='2016-12' then a.PayAmount end) as '2016-12',
SUM(case when CONVERT(varchar(7),a.CreateTime,120)='2017-01' then a.PayAmount end) as '2017-01',
SUM(case when CONVERT(varchar(7),a.CreateTime,120)='2017-02' then a.PayAmount end) as '2017-02'
from PartInChina_cn.dbo.ORD_Purchase a
join PartInChina_cn.dbo.ORD_Sales b on a.PNO=b.PO_NO
left join PartInChina_cn.dbo.ORD_RefundAudit c on a.PNO=c.PNO
where a.CreateTime>'2016-1-1' and a.CreateTime<'2017-3-1'
and a.Status>=3 and a.Status!=11 and a.OrderType in (0,2) and c.PNO is null
group by b.MemberName