如何把纵向记录变成横向记录
如图所示：
------>
我用INSERT INTO ls1 (km1,km1mc) ;
   SELECT * FROM kmk ;
      WHERE code$'100,200,300'
BROWSE
INSERT INTO ls1 (km2,km2mc) ;
   SELECT * FROM kmk ;
      WHERE !code$'100,200,300'
如图

有何办法把km2,km2mc中的空不产生？笨办法加上相同的101,102,103，然后唯一索引，有没有更好的办法？一次性产生无空格？

用你的方法，先生成2个表，然后在合并，这样改变比较小
如：
km1字段的表名为 tb_km1,km2字段的表名为 tb_km2
分别用你的方法，对应生成 tb_km1 和 tb_km2,用下面的方法合并
rc1=recc(tb_km1)
rc2=recc(tb_km2)
if rc1>rc2
   select k1.km1,k1.km1mc,k2.km2,k2.km2mc from (select km1,km2,space(10) as km2,space(30) as km2mc,recn() as rn from tb_km1)k1 ;
   left join (select space(10) as km1,space(30) as km1mc,km2,km2mc,recn() as rn from tb_km2)k2 on k1.rn=k2.rn ;
   into table tb_km
else
   select k1.km1,k1.km1mc,k2.km2,k2.km2mc from (select km1,km2,space(10) as km2,space(30) as km2mc,recn() as rn from tb_km1)k1 ;
   right join (select space(10) as km1,space(30) as km1mc,km2,km2mc,recn() as rn from tb_km2)k2 on k1.rn=k2.rn ;
   into table tb_km
end

mywisdom88班主：
原表是二列纵向记录，我想变成四列，这样一一对应，我的做法，先生成一个有四列的空表，然后插入进去，发现第三，四列前面有空记录，用笨办法是空记录中输入三条对应的记录，然后唯一索引。我问的有否好办法一次产生没有空格的表。

[此贴子已经被作者于2017-3-11 11:37编辑过]

可以参考：https://blog.bccn.net/sdta/16715

都说了，用你的方法，但你的方法是生成1个表，只是现在要生成2个表，然后，按2楼的方法合并。或者你用4楼的方法

生成2个过度表 tb_ls1,tb_ls2
INSERT INTO tb_ls1 (km1,km1mc) ;
   SELECT * FROM kmk ;
      WHERE code$'100,200,300'
*BROWSE
INSERT INTO tb_ls2 (km2,km2mc) ;
   SELECT * FROM kmk ;
      WHERE !code$'100,200,300'
**合并
rc1=recc(tb_ls1)
rc2=recc(tb_ls2)
if rc1>rc2
   select k1.km1,k1.km1mc,k2.km2,k2.km2mc from (select km1,km2,space(10) as km2,space(30) as km2mc,recn() as rn from tb_ls1)k1 ;
   left join (select space(10) as km1,space(30) as km1mc,km2,km2mc,recn() as rn from tb_ls2)k2 on k1.rn=k2.rn ;
   into table ls1
else
   select k1.km1,k1.km1mc,k2.km2,k2.km2mc from (select km1,km2,space(10) as km2,space(30) as km2mc,recn() as rn from tb_ls1)k1 ;
   right join (select space(10) as km1,space(30) as km1mc,km2,km2mc,recn() as rn from tb_ls2)k2 on k1.rn=k2.rn ;
   into table ls1
end

纸上谈兵是没用的，上传相关文件，转换后所达到的格式

mywisdom88班主
按你的代码试了一下，提示找不到km2等等。

* 建立测试数据 Kmk
*CREATE CURSOR kmk(Code C(3),Name C(40))
CREATE CURSOR kmk(Code int,Name C(40))
INSERT INTO kmk(Code,Name) VALUES (100,'教育基础知识（高中、初中）')
INSERT INTO kmk(Code,Name) VALUES (101,'语文（高中、初中）')
INSERT INTO kmk(Code,Name) VALUES (102,'数学（高中、初中）')
INSERT INTO kmk(Code,Name) VALUES (103,'英语（高中、初中）')
INSERT INTO kmk(Code,Name) VALUES (200,'教育基础知识（小学）')
INSERT INTO kmk(Code,Name) VALUES (201,'语文（小学）')
INSERT INTO kmk(Code,Name) VALUES (202,'数学（小学）')
INSERT INTO kmk(Code,Name) VALUES (203,'英语（小学）')

*BROWSE
**合并
select Code ,Name from Kmk WHERE INLIST(Code,100,200,300) INTO CURSOR tb_ls1
select Code ,Name from Kmk WHERE NOT INLIST(Code,100,200,300) INTO CURSOR tb_ls2

rc1 = RECCOUNT("tb_ls1")
rc2 = RECCOUNT("tb_ls2")

   select IIF(ISNULL(k1.km1),0,k1.km1) as km1,IIF(ISNULL(k1.km1mc),'',k1.km1mc) as km1mc,IIF(ISNULL(k2.km2),0,k2.km2) as km2,IIF(ISNULL(k2.km2mc),'',k2.km2mc) as km2mc;
   from (select Code as km1,Name as km1mc,space(3) as km2,space(40) as km2mc,recn() as rn from tb_ls1)k1 ;
   right join (select space(3) as km1,space(40) as km1mc,Code as km2,Name as km2mc,recn() as rn from tb_ls2)k2 on k1.rn=k2.rn ;
   into CURSOR ls1
BROWSE

**另外，建议你 Code 用字符，这样才不会显示0，如果为字符，就要修改IIF(ISNULL(k1.km1),'',k1.km1)其他的相同

* 建立测试数据 Kmk,Code 为字符串
CREATE CURSOR kmk(Code C(3),Name C(40))
INSERT INTO kmk(Code,Name) VALUES ('100','教育基础知识（高中、初中）')
INSERT INTO kmk(Code,Name) VALUES ('101','语文（高中、初中）')
INSERT INTO kmk(Code,Name) VALUES ('102','数学（高中、初中）')
INSERT INTO kmk(Code,Name) VALUES ('103','英语（高中、初中）')
INSERT INTO kmk(Code,Name) VALUES ('200','教育基础知识（小学）')
INSERT INTO kmk(Code,Name) VALUES ('201','语文（小学）')
INSERT INTO kmk(Code,Name) VALUES ('202','数学（小学）')
INSERT INTO kmk(Code,Name) VALUES ('203','英语（小学）')

*BROWSE
**合并
select Code ,Name from Kmk WHERE INLIST(Code,'100','200','300') INTO CURSOR tb_ls1
select Code ,Name from Kmk WHERE NOT INLIST(Code,'100','200','300') INTO CURSOR tb_ls2

rc1 = RECCOUNT("tb_ls1")
rc2 = RECCOUNT("tb_ls2")
IF rc1>rc2
   SELECT IIF(ISNULL(k1.km1),'',k1.km1) as km1,IIF(ISNULL(k1.km1mc),'',k1.km1mc) as km1mc,IIF(ISNULL(k2.km2),'',k2.km2) as km2,IIF(ISNULL(k2.km2mc),'',k2.km2mc) as km2mc;
    FROM (select Code as km1,Name as km1mc,space(3) as km2,space(40) as km2mc,recn() as rn from tb_ls1)k1 ;
    left join (select space(3) as km1,space(40) as km1mc,Code as km2,Name as km2mc,recn() as rn from tb_ls2)k2 on k1.rn=k2.rn ;
    into CURSOR ls1
ELSE
   SELECT IIF(ISNULL(k1.km1),'',k1.km1) as km1,IIF(ISNULL(k1.km1mc),'',k1.km1mc) as km1mc,IIF(ISNULL(k2.km2),'',k2.km2) as km2,IIF(ISNULL(k2.km2mc),'',k2.km2mc) as km2mc;
    FROM (select Code as km1,Name as km1mc,space(3) as km2,space(40) as km2mc,recn() as rn from tb_ls1)k1 ;
    right join (select space(3) as km1,space(40) as km1mc,Code as km2,Name as km2mc,recn() as rn from tb_ls2)k2 on k1.rn=k2.rn ;
    into CURSOR ls1
ENDIF
   
BROWSE