急急急！！求SQL语句
原文档结构如下：
cfname rate
47586844 t f.txt 521.30
47586846 t f.txt 56.23
47586845 f.txt 526.00
47586844 t c.txt 473.00
47586846 t c.txt 0.13
要求生成结构如下：
cfname frate
47586844 t.txt 521.30 473.00
47586846 t.txt 56.23 0.13
47586845 f.txt 526.00 0.00
文件名都是“ f”或“ c”结束（不是后缀名），处理后，把“ f”和“ c“之前相同的放在一行，且” f“的rate在前，” c”在后，缺少“ f” 或“ c”的，补零，生成另外一张表
非常急
[解决办法]
SELECT LEFT(CFNAME,9),;
SUM(IIF(SUBSTR(cfname,10)='t f.txt' OR SUBSTR(cfname,10)='f.txt', rate,00000.00)),;
SUM(IIF(SUBSTR(cfname,10)='t c.txt', rate,00000.00));
into dbf newtt FROM TT1 GROUP BY 1
[解决办法]
就是交叉表
SELECT LEFT(CFNAME,9),;
SUM(IIF(SUBSTR(cfname,10)='t f.txt' OR SUBSTR(cfname,10)='f.txt', rate,00000.00)),;
SUM(IIF(SUBSTR(cfname,10)='t c.txt', rate,00000.00));
into dbf newtt FROM TT1 GROUP BY 1
or
SELECT LEFT(CFNAME,9),;
max(IIF(SUBSTR(cfname,10)='t f.txt' OR SUBSTR(cfname,10)='f.txt', rate,00000.00)),;
max(IIF(SUBSTR(cfname,10)='t c.txt', rate,00000.00));
into dbf newtt FROM r:\temp\TT1 GROUP BY 1
