如何用sql语句生成如下视图 我有点转不过弯

wwwwa

如只有两列的话：

select xm,first(fs),
iif(first(fs)=last(fs),0,last(fs)) from a20
group by xm

wwwwa

以下是引用liuyi_yt在2006-10-11 17:16:00的发言：


不止两条记录怎么写，  而且mssql中应该怎么写

1、这里是ACCESS论坛；

2、基本思路：添加辅助列，自增字段，在SQL　SERVER中用IDENTITY

SELECT * into ff FROM (SELECT (select count(*) from a20 where a.id>=id and a.xm=xm) as xh ,*
FROM a20 a
ORDER BY xm, xh)

SELECT XM,SUM(IIF(XH=1,FS,0)),SUM(IIF(XH=2,FS,0)),SUM(IIF(XH=3,FS,0))..... from ff

GROUP BY XM
相对而言，ACCESS简单得多，

TRANSFORM first(fs)
SELECT xm
FROM ff
GROUP BY xm
PIVOT xh;