vf表行列互转的问题

[fireguard]

如何在vf中使用select命令将下表


转换成

xm 语文 数学 地理
张三
李二
汪一
马六


的格式


反过来如何转换呢？

[lygcw9602]

SELECT XM,SUM(IIF(ALLTRIM(KM)==[语文],FS,0)) 语文,;
  SUM(IIF(ALLTRIM(KM)==[数学],FS,0)) 数学,;
  SUM(IIF(ALLTRIM(KM)==[地理],FS,0)) 地理 FROM T GROUP BY XM INTO CURSOR T1
BROWSE

[lygcw9602]

续1楼

SELECT XM,语文 FS,[语文] KM FROM T1 WHERE NOT EMPTY(语文) ;
  UNION (SELECT XM,数学 FS,[数学] KM FROM T1 WHERE NOT EMPTY(数学)) ;
  UNION (SELECT XM,地理 FS,[地理] KM FROM T1 WHERE NOT EMPTY(地理)) INTO CURSOR T2
BROWSE

[十豆三]

Create Cursor T1 (XM C(10),KM C(10),FS I)
Insert Into T1 Values ('张三','语文',60)
Insert Into T1 Values ('李二','语文',61)
Insert Into T1 Values ('汪一','数学',58)
Insert Into T1 Values ('马六','地理',91)
Insert Into T1 Values ('马六','数学',60)
Insert Into T1 Values ('李二','地理',30)
Insert Into T1 Values ('张三','数学',20)
Insert Into T1 Values ('张三','地理',39)
*-- 以上生成测试数据，这里假设你的原表名为 T1

Select KM Into Cursor TMP From T1 Group By KM
Select TMP
lcStr="XM"
Scan
	lcStr=lcStr+",Sum(Iif(KM='"+Alltrim(KM)+"',FS,0)) As "+Alltrim(KM)
Endscan
Select &lcStr. From T1 Group By xm

[lygcw9602]

CREATE CURSOR T1 (XM C(10),KM C(10),FS I)
INSERT INTO T1 VALUES ('张三','语文',60)
INSERT INTO T1 VALUES ('李二','语文',61)
INSERT INTO T1 VALUES ('汪一','数学',58)
INSERT INTO T1 VALUES ('马六','地理',91)
INSERT INTO T1 VALUES ('马六','数学',60)
INSERT INTO T1 VALUES ('李二','地理',30)
INSERT INTO T1 VALUES ('张三','数学',20)
INSERT INTO T1 VALUES ('张三','地理',39)
*-- 以上生成测试数据，这里假设你的原表名为 T1

*--- 行记录转换为列记录
SELECT KM INTO CURSOR TMP FROM T1 GROUP BY KM
SELECT TMP
LCSTR="SELECT XM"
SCAN
    LCSTR=LCSTR+",SUM(IIF(ALLTRIM(KM)=='"+ALLTRIM(KM)+"',FS,0)) AS "+ALLTRIM(KM)+;
     IIF(RECNO()<RECCOUNT(),[],[ FROM T1 GROUP BY XM INTO CURSOR T2])
ENDSCAN
MESSAGEBOX(LCSTR)
&LCSTR
BROWSE

*--- 列记录转换为行记录
SELECT T2
LCSTR=[]
FOR I=2 TO FCOUNT()
    LCSTR=LCSTR+'SELECT XM,FIELD('+TRANSFORM(I)+') AS KM,EVALUATE(FIELD('+TRANSFORM(I)+')) AS FS FROM T2 WHERE NOT EMPTY(EVALUATE(FIELD('+TRANSFORM(I)+')))'+;
    IIF(I>2,')',[])+IIF(I<FCOUNT(),' UNION (',' INTO CURSOR T3')
ENDFOR
=MESSAGEBOX(LCSTR)
&LCSTR
BROWSE