求定长模型添加笛卡尔记录拼接SQL问题

现有动态表A，表中无记录只有字段 AID
需要拼接SQL生成初始表记录数，
记录数范围 A1字段集合内容为 ACont1，ACont2；B1字段集合内容为BCont1，BCont2，BCont3
动态表A（定长）记录数应为 2×3条并根据插入的记录动态生成AID。
我的问题是
经过一番折腾实际插入表中为如下内容：
表头 AID | A1 | B1
AID01 ACont1 NULL
AID02 ACont2 NULL
AID03 NULL BCont1
AID04 Null BCont2
AID05 Null BCont3
AID06 Null BCont1

而我需要的却是如下表
表头 AID | A1 | B1
AID01 ACont1 BCont2
AID02 ACont2 BCont3
AID03 ACont1 BCont1
AID04 ACont2 BCont2
AID05 ACont1 BCont3
AID06 ACont2 BCont1

请大侠们帮个忙，整理下处理思路谢谢！

现附上处理代码：
/**插入定常表单预先内容
* */
public void insItemValue(InfoItem infoitem) throws DAOException{
try{
StringBuffer sbs = new StringBuffer();
//开始自动扩展定常表中记录行数根据维度组 如果是定长表则查询定长表需要自动扩展插入数据的维度组
List codeitemlist = findWeiDuItem(infoItem.getItemCodeSet()); //获得维度ID
List scode = new ArrayList();
int is = 0;
int cis = 0;
for(Iterator itcode = codeitemlist.iterator();itcode.hasNext();){
HashMap chmp = (HashMap) itcode.next();
String syc = chmp.values().toString();
String chs = syc.substring(1,syc.length()-1);
scode.add(is++,chs);
}
int codeitemcount = findWeiDuCount(infoItem.getItemCodeSet());//获得维度记录数
int cont =codeitemcount;
if(codeitemcount == 0) codeitemcount = 1;
List lisJiGou = findTableJieGou(infoItem.getSetId());//获得此表列
HashMap hm = (HashMap)lisJiGou.get(0);//因表中字段不允许删除，所以按降序排列的取第一个就是最新表头
String sy = hm.values().toString();
String newAddHard= sy.substring(1,sy.length()-1); //获取新加列头
int tableitemCount = findTableCount(infoItem.getSetId());//获取表中记录数
int tableitemCountLoad = tableitemCount; //赋值被减记录数为原记录数
if(tableitemCount == 0) {
tableitemCount = 1; //如果记录数为零则定义表中记录数唯1
tableitemCountLoad = 0; //并赋值需要减去的行数为0
}
int tableansitemCount = tableitemCount*codeitemcount-tableitemCountLoad; //获得需要插入记录数的条数
Connection conn=s.connection();
Statement stat=conn.createStatement();

for(int c = 0 ;c<tableansitemCount;c++){ //循环拼接插入SQL并插入记录
sbs.delete(0,sbs.length());
sbs.append("insert into ").append(infoItem.getSetId()).append(" (");
sbs.append(newAddHard).append(",").append(infoItem.getSetId()).append(") values('");
codeitemcount = codeitemcount-1;
String sc = scode.get(codeitemcount).toString();
if(codeitemcount == 0) codeitemcount = cont;
sbs.append(sc).append("','");
String newId = null;
newId = SysMessageTool.getnewIdaddone(getItemValueId(infoItem.getSetId()),c);
sbs.append(newId).append("')");
stat.execute(sbs.toString());
}
stat.close();
} catch(Exception e){
throw new DAOException( "预先插入定常表记录出错.", e, this.getClass());
}
}

如果此表新添字段A2 A2内容为A2Cont1，A2Cont2
则定长模型变为 原来2×3×2 条记录 则相应的值也要对上并且不能有重复内容的化我该怎么去解决？

如果是oralce，可以用以下的sql

INSERT INTO A
(
    AID,
    A1,
    B1,
    A2
)
SELECT 
    'AID' || LPAD(row_number() over(ORDER BY X.A1, Y.B1, Z.A2), 2, '0') AS AID,
    --'AID' || LPAD(ROWNUM, 2, '0') AS AID,
    X.A1,
    Y.B1,
    Z.A2
FROM (
    (
        SELECT 'ACont1' AS A1 FROM DUAL
        UNION 
        SELECT 'ACont2' AS A1 FROM DUAL
    ) X INNER JOIN 
    (
        SELECT 'BCont1' AS B1 FROM DUAL
        UNION 
        SELECT 'BCont2' AS B1 FROM DUAL
        UNION 
        SELECT 'BCont3' AS B1 FROM DUAL
    ) Y ON 1 = 1
    INNER JOIN 
    (
        SELECT 'A2Cont1' AS A2 FROM DUAL
        UNION 
        SELECT 'A2Cont2' AS A2 FROM DUAL
    ) Z ON 1 = 1
)

太感谢了 搞定！谢谢armorking ！

armorking
我现在有另外一个问题请教下你！
在原来你解答的问题基础上，此定常表用过一段时间，我如果新加字段C 并且新字段的范围是CCont1，CCont2，
又该怎么做？

在原来基础上改一下就好了

INSERT INTO A
(
    AID,
    A1,
    B1,
    A2,
    C
)
SELECT 
    'AID' || LPAD(row_number() over(ORDER BY X.A1, Y.B1, Z.A2, W.C), 2, '0') AS AID,
    --'AID' || LPAD(ROWNUM, 2, '0') AS AID,
    X.A1,
    Y.B1,
    Z.A2,
    W.C
FROM (
    (
        SELECT 'ACont1' AS A1 FROM DUAL
        UNION 
        SELECT 'ACont2' AS A1 FROM DUAL
    ) X 
    INNER JOIN 
    (
        SELECT 'BCont1' AS B1 FROM DUAL
        UNION 
        SELECT 'BCont2' AS B1 FROM DUAL
        UNION 
        SELECT 'BCont3' AS B1 FROM DUAL
    ) Y ON 1 = 1
    INNER JOIN 
    (
        SELECT 'A2Cont1' AS A2 FROM DUAL
        UNION 
        SELECT 'A2Cont2' AS A2 FROM DUAL
    ) Z ON 1 = 1
    INNER JOIN 
    (
        SELECT 'CCont1' AS C FROM DUAL
        UNION 
        SELECT 'CCont2' AS C FROM DUAL
    ) W ON 1 = 1
)

一般来讲，一个系统在碰到增加字段的时候，通常都是需要修改程序的
但是，应当可以尽量简单地对应这种变化

    /**
     * 生成定常表处理 此动态表中主键为表名＋ID
     * @param infoItemSetId 表名
     * @param infoitemBolist 表列
     * @param weiduListValue 列维度
     * @param userId 当前操作用户
     * */
    public void initInsItemValue(String infoItemSetId,List infoitemBolist,List weiduListValue,String userId) throws DAOException{
            //拼接SQL中需要动态处理的地方

    try{

            StringBuffer siccSql = new StringBuffer();
            StringBuffer siccSql2 = new StringBuffer();
            StringBuffer siccSql3 = new StringBuffer();
            StringBuffer weiduSql = new StringBuffer();
            siccSql.append("INSERT INTO ").append(infoItemSetId).append(" (").append(infoItemSetId).append(",");
            for(int i = 0 ; i<infoitemBolist.size();i++){
                String chss = infoitemBolist.get(i).toString();
                siccSql.append(chss);
                siccSql2.append(chss).append(".").append(chss);
                siccSql3.append(chss).append(".").append(chss);
                 if(i == infoitemBolist.size()-1){
                     siccSql.append(" ");
                     siccSql2.append(" ");
                     siccSql3.append(" ");
                     thisTableId = chss;
                 }else{
                     siccSql.append(",");
                     siccSql2.append(",");
                     siccSql3.append(",");
                 }               
            }

          for(int j = 0 ; j<weiduListValue.size();j++){
                    String chss2 = infoitemBolist.get(j).toString();
                    ArrayList weidulis1 = (ArrayList) weiduListValue.get(j);
                          if(j == 0 ){
                              weiduSql.append("");
                          }else{
                              weiduSql.append(" INNER JOIN ");
                          }
                          weiduSql.append("(");
                          for(int ww = 0 ;ww<weidulis1.size();ww++){
                            HashMap weiduhs =(HashMap) weidulis1.get(ww);
                            String codeid  = weiduhs.get("code_item_id").toString();
                            String chsswd = codeid;
                            weiduSql.append(" SELECT '").append(chsswd).append("' AS ").append(chss2).append(" FROM DUAL");
                            if( ww == weidulis1.size()-1){
                                weiduSql.append(" ");
                            }else{
                                weiduSql.append(" UNION ");
                            }
                          }
                          weiduSql.append(") ").append(chss2);
                          if(j == 0){
                             weiduSql.append(" ");
                          }else{
                             weiduSql.append(" ON 1 = 1");
                          }
                }
            
            siccSql.append(") SELECT ").append("'")
                    .append(infoItemSetId).append("'")
                    .append(" || LPAD(row_number() over(ORDER BY ")
                    .append(siccSql2).append("), 6, '0') AS ").append(thisTableId).append(",")
                    .append(siccSql3).append(" FROM (").append(weiduSql).append(")");

          Connection conn=s.connection();
          Statement stat=conn.createStatement();
          try{
          stat.execute(siccSql.toString());
          stat.close();
          }catch (Exception e){
              conn.rollback();

              throw new DAOException( "预先插入定常表记录出错.", e, this.getClass());
          }

    }catch (Exception e){    }
 }

 再次感谢armorking
方法处理完成！