横表变竖表的问题,高手帮忙

Question

现有航班信息表:
T_FltInfo
CARRIERID FLTNO FLTDATE       IO APT
CZ         123 2008-12-31    I         OAK,ANC,NRT,PEK,现要求得到航段信息视图
V_FltInfo
CARRIERID FLTNO FLTDATE       IO SAPT   EAPT
CZ         123 2008-12-31    I         OAK    ANC
CZ         123 2008-12-31    I         OAK    NRT
CZ         123 2008-12-31    I         OAK    PEK
CZ         123 2008-12-31    I         ANC    NRT
CZ         123 2008-12-31    I         ANC    PEK
CZ         123 2008-12-31    I         NRT    PEK高手请回答一下,着急呀,谢谢了!
注:我要求得到一个视图,因为这个视图还得跟别的表做关联

Answer 1

看这个链接的3楼.只要第一层查询就可以了:
http://topic.csdn.net/u/20081225/17/1d9e676a-7faf-4a49-88db-f0836b53bf28.html

Answer 2

-- TRY IT ..
SQL> SELECT * FROM TABLE_NAME;CARRIERID      FLTNO FLTDATE     IO APT
--------- ---------- ----------- -- ----------------
CZ               123 2008-12-31  I  OAK,ANC,NRT,PEK,SQL> SELECT CARRIERID, FLTNO, FLTDATE, IO, NEW_APT_T1, NEW_APT_T2
  2    FROM (SELECT T1.CARRIERID,
  3                 T1.FLTNO,
  4                 T1.FLTDATE,
  5                 T1.IO,
  6                 NEW_APT_T1,
  7                 NEW_APT_T2,
  8                 ROW_NUMBER() OVER(PARTITION BY T1.CARRIERID, T1.FLTNO, T1.FLTDATE, T1.IO, ASCII(NEW_APT_T1) + ASCII(NEW_APT_T2) ORDER BY NEW_APT_T1, NEW_APT_T2) RN
  9            FROM (SELECT TT.*,
 10                         SUBSTR(',' || TRIM(',' FROM APT) || ',',
 11                                INSTR(',' || TRIM(',' FROM APT) || ',',
 12                                      ',',
 13                                      1,
 14                                      RN) + 1,
 15                                INSTR(',' || TRIM(',' FROM APT) || ',',
 16                                      ',',
 17                                      1,
 18                                      RN + 1) - INSTR(',' || TRIM(',' FROM APT) || ',',
 19                                                      ',',
 20                                                      1,
 21                                                      RN) - 1) NEW_APT_T1
 22                    FROM TABLE_NAME TT,
 23                         (SELECT ROWNUM RN
 24                            FROM ALL_OBJECTS
 25                           WHERE ROWNUM <=
 26                                 (SELECT MAX(LENGTH(TRIM(',' FROM APT)) -
 27                                             LENGTH(REPLACE(TRIM(',' FROM APT),
 28                                                            ',',
 29                                                            '')))
 30                                    FROM TABLE_NAME TT) + 1) ZZ
 31                   WHERE INSTR(',' || TRIM(',' FROM APT) || ',', ',', 1, RN) > 0) T1,
 32                 (SELECT TT.*,
 33                         SUBSTR(',' || TRIM(',' FROM APT) || ',',
 34                                INSTR(',' || TRIM(',' FROM APT) || ',',
 35                                      ',',
 36                                      1,
 37                                      RN) + 1,
 38                                INSTR(',' || TRIM(',' FROM APT) || ',',
 39                                      ',',
 40                                      1,
 41                                      RN + 1) - INSTR(',' || TRIM(',' FROM APT) || ',',
 42                                                      ',',
 43                                                      1,
 44                                                      RN) - 1) NEW_APT_T2
 45                    FROM TABLE_NAME TT,
 46                         (SELECT ROWNUM RN
 47                            FROM ALL_OBJECTS
 48                           WHERE ROWNUM <=
 49                                 (SELECT MAX(LENGTH(TRIM(',' FROM APT)) -
 50                                             LENGTH(REPLACE(TRIM(',' FROM APT),
 51                                                            ',',
 52                                                            '')))
 53                                    FROM TABLE_NAME TT) + 1) ZZ
 54                   WHERE INSTR(',' || TRIM(',' FROM APT) || ',', ',', 1, RN) > 0) T2
 55           WHERE T1.CARRIERID = T2.CARRIERID
 56             AND T1.FLTNO = T2.FLTNO
 57             AND T1.FLTDATE = T2.FLTDATE
 58             AND T1.IO = T2.IO
 59             AND NEW_APT_T1 <> NEW_APT_T2) YY
 60   WHERE RN = 1;CARRIERID      FLTNO FLTDATE     IO NEW_APT_T1        NEW_APT_T2
--------- ---------- ----------- -- ----------------- -----------------
CZ               123 2008-12-31  I  ANC               NRT
CZ               123 2008-12-31  I  ANC               OAK
CZ               123 2008-12-31  I  ANC               PEK
CZ               123 2008-12-31  I  NRT               OAK
CZ               123 2008-12-31  I  NRT               PEK
CZ               123 2008-12-31  I  OAK               PEK6 rows selected

Answer 3

MantisXF:
你可真是大好人,但现在你的脚本从我这儿执行时出错,我将建表的脚本帖出来,你好事做到底,再帮我看一下,实在是太复杂了,我都看不懂啊.create table T_FltInfo
(
  CARRIERID VARCHAR2(2) not null,
  FLTNO     VARCHAR2(20) not null,
  FLTDATE   DATE not null,
  IO        CHAR(1) not null,
  APT      VARCHAR2(64)
)select * from T_FltInfo;
insert into T_FltInfo Values ('CZ','123',To_date('2008-12-31','yyyy-MM-dd'),'I','OAK,ANC,NRT,PEK,PVG,ANC,');
commit;

Answer 4

高人，改正这样：
SELECT CARRIERID, FLTNO, FLTDATE, IO, NEW_APT_T1, NEW_APT_T2
      FROM (SELECT T1.CARRIERID,
                   T1.FLTNO,
                  T1.FLTDATE,
                  T1.IO,
                  NEW_APT_T1,
                  NEW_APT_T2,
                  ROW_NUMBER() OVER(PARTITION BY T1.CARRIERID, T1.FLTNO, T1.FLTDATE, T1.IO, ASCII(NEW_APT_T1) + ASCII(NEW_APT_T2) ORDER BY NEW_APT_T1, NEW_APT_T2) RN
             FROM (SELECT TT.*,
                         SUBSTR(',' || TRIM(',' FROM APT) || ',',
                               INSTR(',' || TRIM(',' FROM APT) || ',',
                                       ',',
                                       1,
                                      RN) + 1,
                              INSTR(',' || TRIM(',' FROM APT) || ',',
                                     ',',
                                      1,
                                      RN + 1) - INSTR(',' || TRIM(',' FROM APT) || ',',
                                                      ',',
                                                      1,
                                                      RN) - 1) NEW_APT_T1
                    FROM T_FltInfo TT,
                         (SELECT ROWNUM RN
                            FROM ALL_OBJECTS
                           WHERE ROWNUM <=
                                 (SELECT MAX(LENGTH(TRIM(',' FROM APT)) -
                                              LENGTH(REPLACE(TRIM(',' FROM APT),
                                                             ',',
                                                             '')))
                                    FROM T_FltInfo TT) + 1) ZZ
                   WHERE INSTR(',' || TRIM(',' FROM APT) || ',', ',', 1, RN) > 0) T1,
                (SELECT TT.*,
                         SUBSTR(',' || TRIM(',' FROM APT) || ',',
                                INSTR(',' || TRIM(',' FROM APT) || ',',
                                      ',',
                                      1,
                                      RN) + 1,
                                INSTR(',' || TRIM(',' FROM APT) || ',',
                                       ',',
                                  1,
                                     RN + 1) - INSTR(',' || TRIM(',' FROM APT) || ',',
                                                    ',',
                                                     1,
                                                      RN) - 1) NEW_APT_T2
                   FROM T_FltInfo TT,
                         (SELECT ROWNUM RN
                            FROM ALL_OBJECTS
                            WHERE ROWNUM <=
                                  (SELECT MAX(LENGTH(TRIM(',' FROM APT)) -
                                            LENGTH(REPLACE(TRIM(',' FROM APT),
                                                            ',',
                                                            '')))
                                    FROM T_FltInfo TT) + 1) ZZ
                   WHERE INSTR(',' || TRIM(',' FROM APT) || ',', ',', 1, RN) > 0) T2
           WHERE T1.CARRIERID = T2.CARRIERID
             AND T1.FLTNO = T2.FLTNO
              AND T1.FLTDATE = T2.FLTDATE
              AND T1.IO = T2.IO
             AND NEW_APT_T1 <> NEW_APT_T2) YY
    WHERE RN = 1;

Answer 5

谢谢各位,执行通过.但现在数据有些问题,
我的设想是:如果APT字段是  "AAA,BBB,CCC,DDD,EEE,"
则应该出现的记录中因该是
AAA,BBB
AAA,CCC
AAA,DDD
AAA,EEE
BBB,CCC
BBB,DDD
BBB,EEE
CCC,DDD
CCC,EEE
DDD,EEE
当APT字段中能分割出5个值时,应该能生成 4+3+2+1条记录,以上4个值的测试结果是对的,但是当APT字段中的值增加到5个时就不对了阿,请你们再帮帮我,谢谢

Answer 6

 如果你的表中io arp中城市不是固定数量，最好使用存储过程处理数据到一个中间表，再做后续处理。

Answer 7

试过MantisXF的方法，可行，太强了！！

Answer 8

SQL> with tp_sql as
  2  (select b.rid,
  3                 replace(substr('OAK,ANC,NRT,PEK,',
  4                                decode(b.rid,
  5                                       1,
  6                                       1,
  7                                       instr('OAK,ANC,NRT,PEK,',
  8                                             ',',
  9                                             1,
 10                                             b.rid - 1)),
 11                                instr('OAK,ANC,NRT,PEK,', ',', 1, b.rid) -
 12                                decode(b.rid,
 13                                       1,
 14                                       1,
 15                                       instr('OAK,ANC,NRT,PEK,',
 16                                             ',',
 17                                             1,
 18                                             b.rid - 1))),
 19                         ',',
 20                         '') name
 21            from dual a,
 22                 (select rownum rid
 23                    from all_objects
 24                   where rownum <=
 25                         (select length('OAK,ANC,NRT,PEK,') -
 26                                 length(replace('OAK,ANC,NRT,PEK,', ',', '')) 27                            from dual)) b)
 28  select a.name, b.name
 29    from tp_sql a,
 30         tp_sql b
 31   where b.rid > a.rid;NAME       NAME
---------- ----------
OAK        ANC
OAK        NRT
ANC        NRT
OAK        PEK
ANC        PEK
NRT        PEK已选择6行。SQL> /NAME       NAME
---------- ----------
OAK        ANC
OAK        NRT
ANC        NRT
OAK        PEK
ANC        PEK
NRT        PEK已选择6行。SQL> with tp_sql as
  2  (select b.rid,
  3                 replace(substr('AAA,BBB,CCC,DDD,EEE,',
  4                                decode(b.rid,
  5                                       1,
  6                                       1,
  7                                       instr('AAA,BBB,CCC,DDD,EEE,',
  8                                             ',',
  9                                             1,
 10                                             b.rid - 1)),
 11                                instr('AAA,BBB,CCC,DDD,EEE,', ',', 1, b.rid)
-
 12                                decode(b.rid,
 13                                       1,
 14                                       1,
 15                                       instr('AAA,BBB,CCC,DDD,EEE,',
 16                                             ',',
 17                                             1,
 18                                             b.rid - 1))),
 19                         ',',
 20                         '') name
 21            from dual a,
 22                 (select rownum rid
 23                    from all_objects
 24                   where rownum <=
 25                         (select length('AAA,BBB,CCC,DDD,EEE,') -
 26                                 length(replace('AAA,BBB,CCC,DDD,EEE,', ',',
''))
 27                            from dual)) b)
 28  select a.name, b.name
 29    from tp_sql a,
 30         tp_sql b
 31   where b.rid > a.rid;NAME       NAME
---------- ----------
AAA        BBB
AAA        CCC
BBB        CCC
AAA        DDD
BBB        DDD
CCC        DDD
AAA        EEE
BBB        EEE
CCC        EEE
DDD        EEE

Answer 9

你这个是因为 ASCII(AAA)+ASCII(DDD)=ASCII(BBB)+ASCII(CCC)和
 ASCII(BBB)+ASCII(EEE)=ASCII(DDD)+(CCC)改成下面这样就可以了SELECT CARRIERID, FLTNO, FLTDATE, IO, NEW_APT_T1, NEW_APT_T2
  FROM (SELECT T1.CARRIERID,
               T1.FLTNO,
               T1.FLTDATE,
               T1.IO,
               NEW_APT_T1,
               NEW_APT_T2,
               ROW_NUMBER() OVER(PARTITION BY T1.CARRIERID, T1.FLTNO, T1.FLTDATE, T1.IO, ASCII(NEW_APT_T1) + ASCII(NEW_APT_T2) ORDER BY NEW_APT_T1, NEW_APT_T2) RN
          FROM (SELECT TT.*,
                       SUBSTR(',' || TRIM(',' FROM APT) || ',',
                              INSTR(',' || TRIM(',' FROM APT) || ',',
                                    ',',
                                    1,
                                    RN) + 1,
                              INSTR(',' || TRIM(',' FROM APT) || ',',
                                    ',',
                                    1,
                                    RN + 1) - INSTR(',' || TRIM(',' FROM APT) || ',',
                                                    ',',
                                                    1,
                                                    RN) - 1) NEW_APT_T1
                  FROM T_FltInfo TT,
                       (SELECT ROWNUM RN
                          FROM ALL_OBJECTS
                         WHERE ROWNUM <= (SELECT MAX(LENGTH(TRIM(',' FROM APT)) -
                                                     LENGTH(REPLACE(TRIM(',' FROM APT),
                                                                    ',',
                                                                    '')))
                                            FROM T_FltInfo TT) + 1) ZZ
                 WHERE INSTR(',' || TRIM(',' FROM APT) || ',', ',', 1, RN) > 0) T1,
               (SELECT TT.*,
                       SUBSTR(',' || TRIM(',' FROM APT) || ',',
                              INSTR(',' || TRIM(',' FROM APT) || ',',
                                    ',',
                                    1,
                                    RN) + 1,
                              INSTR(',' || TRIM(',' FROM APT) || ',',
                                    ',',
                                    1,
                                    RN + 1) - INSTR(',' || TRIM(',' FROM APT) || ',',
                                                    ',',
                                                    1,
                                                    RN) - 1) NEW_APT_T2
                  FROM T_FltInfo TT,
                       (SELECT ROWNUM RN
                          FROM ALL_OBJECTS
                         WHERE ROWNUM <= (SELECT MAX(LENGTH(TRIM(',' FROM APT)) -
                                                     LENGTH(REPLACE(TRIM(',' FROM APT),
                                                                    ',',
                                                                    '')))
                                            FROM T_FltInfo TT) + 1) ZZ
                 WHERE INSTR(',' || TRIM(',' FROM APT) || ',', ',', 1, RN) > 0) T2
         WHERE T1.CARRIERID = T2.CARRIERID
           AND T1.FLTNO = T2.FLTNO
           AND T1.FLTDATE = T2.FLTDATE
           AND T1.IO = T2.IO
           AND NEW_APT_T1 <> NEW_APT_T2) YY
 WHERE ASCII(NEW_APT_T1) < ASCII(NEW_APT_T2)
 order by NEW_APT_T1, NEW_APT_T2;
--运行结果：
    CARRIERID FLTNO FLTDATE IO NEW_APT_T1 NEW_APT_T2
1 CZ 123 2008-12-31 I AAA BBB
2 CZ 123 2008-12-31 I AAA CCC
3 CZ 123 2008-12-31 I AAA DDD
4 CZ 123 2008-12-31 I AAA EEE
5 CZ 123 2008-12-31 I BBB CCC
6 CZ 123 2008-12-31 I BBB DDD
7 CZ 123 2008-12-31 I BBB EEE
8 CZ 123 2008-12-31 I CCC DDD
9 CZ 123 2008-12-31 I CCC EEE
10 CZ 123 2008-12-31 I DDD EEE

Answer 10

to Icss_zhen:我的数据库中APT 字段的值是   OAK,ANC,NRT,PEK,PVG,
用你的方法试了一下,还是缺一条数据阿.

Answer 11

谢谢以上各位的指点,我现在明白了些,然后总结了一下,放在我的cnblogs中了,供大家分享,附所有sql语句.
地址:
http://www.cnblogs.com/guodaxia/archive/2009/01/04/1368162.html