列转行问题

有一表TB_A
结构如下：id    m1     m2       m3
--------------------------
001     100    200      300
002     12     22       32
003     22     35       56
...希望的结果：       001    002     003  .......
--------------------------
m1     100    12      22
m2     200    22      35
m3     300    32      56
...列数固定，行数不定，怎么写这个SQL，求教大神，谢谢

解决方案 »

  1.   

    百度动态sql 太多例子了 稍微变动下就可以了
      

  2.   

    看着LZ假日加班学习不容易  花了十几分钟写个存储 lz测试下 是否满足要求.
    --创建测试表
    CREATE TABLE TB_A(ID NVARCHAR2(10),M1 NUMBER,M2 NUMBER,M3 NUMBER);--插入测试数据
    INSERT INTO TB_A VALUES ('001',100,200,300);
    INSERT INTO TB_A VALUES ('002',12,22,32);
    INSERT INTO TB_A VALUES ('003',22,35,56);
    COMMIT;--创建存储过程
    CREATE OR REPLACE PROCEDURE TEST
    IS
      V_SQL NVARCHAR2(2000);
      
      CURSOR CURSOR_1 IS
      select column_name cm
      from user_tab_columns 
      where table_name = 'TB_A' and  column_name <> 'ID';
      
      CURSOR CURSOR_2 IS
      SELECT ID FROM TB_A;    BEGIN
          FOR C_TB IN CURSOR_1
          LOOP
            V_SQL := V_SQL||' SELECT '''||C_TB.CM||''' AS " "';
              FOR V_TB IN CURSOR_2
              LOOP
                V_SQL := V_SQL || ',' || 'SUM(DECODE(A.ID,'''
                      || V_TB.ID || ''', '||C_TB.CM||',0)) AS "'|| V_TB.ID ||'"';
              END LOOP;
              V_SQL := V_SQL || ' FROM TB_A A ' ;
              V_SQL := V_SQL || ' UNION ALL';
          END LOOP;
          V_SQL := SUBSTR(V_SQL,1,LENGTH(V_SQL)-10);
         DBMS_OUTPUT.PUT_LINE(V_SQL);
        --EXECUTE IMMEDIATE V_SQL;
        END;--执行存储过程 返回sql 查询数据          001    002    003
    --------------------------------------
    1 M1 100 12 22
    2 M2 200 22 35
    3 M3 300 32 56--继续插入2行测试数据
    INSERT INTO TB_A VALUES ('004',33,44,55);
    INSERT INTO TB_A VALUES ('005',35,45,65);
    COMMIT;--执行查询 返回结果
              001    002    003    004    005
    -----------------------------------------------
    1 M1 100 12 22 33 35
    2 M2 200 22 35 44 45
    3 M3 300 32 56 55 65
    --删除测试表和存储过程
    DROP TABLE TB_A;
    DROP PROCEDURE TEST;
      

  3.   

    to HJ_daxian:
    我的实际的表中有70多个字段，用您的方法执行到 DBMS_OUTPUT.PUT_LINE(V_SQL);这句时出现如下错误：ORA-20000:ORU-10027: buffer overflow, limit of 10000 bytes
    这个问题也在网上查了一下，大都是用set serveroutput on size 100000的方法，我执行后错误依旧，再请指教，非常感谢
      

  4.   

    字段多了 单行输出sql太长了  可以考虑截断sql 分段打印出来  比如 这种形式
         DBMS_OUTPUT.PUT_LINE(SUBSTR(V_SQL,1,1000));
         DBMS_OUTPUT.PUT_LINE(SUBSTR(V_SQL,1000,1000));
         DBMS_OUTPUT.PUT_LINE(SUBSTR(V_SQL,2000,1000));
         DBMS_OUTPUT.PUT_LINE(SUBSTR(V_SQL,3000,1000));
         DBMS_OUTPUT.PUT_LINE(SUBSTR(V_SQL,4000,1000));
    前提要判断 sql长度是多少 然后根据长度来打印sql 然后不能在关键位置截断 判断空格部分
      

  5.   

    存储过程改了下  根据字段数量来分行打印 
    CREATE OR REPLACE PROCEDURE TEST
    IS
      V_SQL NVARCHAR2(8000);
      V_COUNT NUMBER(10);
      V_BEGIN NUMBER(10);
      V_END NUMBER(10);
      
      CURSOR CURSOR_1 IS
      select column_name cm
      from user_tab_columns 
      where table_name = 'TB_A' and  column_name <> 'ID';
      
      CURSOR CURSOR_2 IS
      SELECT ID FROM TB_A;    BEGIN
          FOR C_TB IN CURSOR_1
          LOOP
            V_SQL := V_SQL||' SELECT '''||C_TB.CM||''' AS " "';
              FOR V_TB IN CURSOR_2
              LOOP
                V_SQL := V_SQL || ',' || 'SUM(DECODE(A.ID,'''
                      || V_TB.ID || ''', '||C_TB.CM||',0)) AS "'|| V_TB.ID ||'"';
              END LOOP;
              V_SQL := V_SQL || ' FROM TB_A A ' ;
              V_SQL := V_SQL || ' UNION ALL';
          END LOOP;
          V_SQL := SUBSTR(V_SQL,1,LENGTH(V_SQL)-10);
          V_COUNT := regexp_count(V_SQL,'UNION ALL');
         V_BEGIN := 1;
         FOR I IN 1..V_COUNT LOOP
              V_END := INSTR(V_SQL,'UNION ALL',1,I);
              DBMS_OUTPUT.PUT_LINE(SUBSTR(V_SQL,V_BEGIN,V_END-V_BEGIN));
              V_BEGIN := V_END;
         END LOOP;
         DBMS_OUTPUT.PUT_LINE(SUBSTR(V_SQL,V_END,LENGTH(V_SQL)-V_END));
        END;
    表中数据：
    执行存储 返回sql 查询的数据
      

  6.   

    感谢HJ_daxian，真是热心人，向您学习现在问题项目oracle版本为10.2，不能使用REGEXP_COUNT函数，还有没有什么办法替代这个REGEXP_COUNT函数呢，再次请教，感谢您的耐心
      

  7.   

    实际表：-- Create table
    create table TB_V2_DBINFO
    (
      JG_ID NVARCHAR2(20) not null,
      MUSER   NVARCHAR2(60),
      M1      NVARCHAR2(50),
      M2      NVARCHAR2(50),
      M3      NVARCHAR2(50),
      M4      NVARCHAR2(50),
      M5      NVARCHAR2(50),
      M6      NVARCHAR2(50),
      M7      NVARCHAR2(50),
      M8      NVARCHAR2(50),
      M9      NVARCHAR2(50),
      M10     NVARCHAR2(50),
      M11     NVARCHAR2(50),
      M12     NVARCHAR2(50),
      M13     NVARCHAR2(50),
      M14     NVARCHAR2(50),
      M15     NVARCHAR2(50),
      M16     NVARCHAR2(50),
      M17     NVARCHAR2(50),
      M18     NVARCHAR2(50),
      M19     NVARCHAR2(50),
      M20     NVARCHAR2(50),
      M21     NVARCHAR2(50),
      M22     NVARCHAR2(50),
      M23     NVARCHAR2(50),
      M24     NVARCHAR2(50),
      M25     NVARCHAR2(50),
      M26     NVARCHAR2(50),
      M27     NVARCHAR2(50),
      M28     NVARCHAR2(50),
      M29     NVARCHAR2(50),
      M30     NVARCHAR2(50),
      M31     NVARCHAR2(50),
      M32     NVARCHAR2(50),
      M33     NVARCHAR2(50),
      M34     NVARCHAR2(50),
      M35     NVARCHAR2(50),
      M36     NVARCHAR2(50),
      M37     NVARCHAR2(50),
      M38     NVARCHAR2(50),
      M39     NVARCHAR2(50),
      M40     NVARCHAR2(50),
      M41     NVARCHAR2(50),
      M42     NVARCHAR2(50),
      M43     NVARCHAR2(50),
      M44     NVARCHAR2(50),
      M45     NVARCHAR2(50),
      M46     NVARCHAR2(50),
      M47     NVARCHAR2(50),
      M48     NVARCHAR2(50),
      M49     NVARCHAR2(50),
      M50     NVARCHAR2(50),
      M51     NVARCHAR2(50),
      M52     NVARCHAR2(50),
      M53     NVARCHAR2(50),
      M54     NVARCHAR2(50),
      M55     NVARCHAR2(50),
      M56     NVARCHAR2(50),
      M57     NVARCHAR2(50),
      M58     NVARCHAR2(50),
      M59     NVARCHAR2(50),
      M60     NVARCHAR2(50),
      M61     NVARCHAR2(50),
      M62     NVARCHAR2(50),
      M63     NVARCHAR2(50),
      M64     NVARCHAR2(50),
      M65     NVARCHAR2(50),
      M66     NVARCHAR2(50),
      M67     NVARCHAR2(50),
      M68     NVARCHAR2(50),
      M69     NVARCHAR2(50),
      M70     NVARCHAR2(50),
      M71     NVARCHAR2(50),
      M72     NVARCHAR2(50),
      M73     NVARCHAR2(50),
      M74     NVARCHAR2(50),
      M75     NVARCHAR2(50),
      M76     NVARCHAR2(50),
      M77     NVARCHAR2(50),
      M78     NVARCHAR2(50),
      M79     NVARCHAR2(50),
      M80     NVARCHAR2(50)
    )
    现在我改了下您的第29行为 V_COUNT := LENGTH(REPLACE(V_SQL, 'UNION ALL', ' '));
    您的存储过程我改为如下： CREATE OR REPLACE PROCEDURE TEST3 IS
      V_SQL   NVARCHAR2(28000);
      V_COUNT NUMBER(10);
      V_BEGIN NUMBER(10);
      V_END   NUMBER(10);  CURSOR CURSOR_1 IS
        select column_name cm
          from user_tab_columns
         where table_name = 'TB_V2_DBINFO'
           and column_name <> 'ZZJG_ID'
           and column_name <> 'MUSER'
           and column_name <> 'UPTIME'
         ORDER BY to_number(substr(cm, 2));  CURSOR CURSOR_2 IS
        SELECT ZZJG_ID FROM TB_V2_DBINFO;BEGIN
      FOR C_TB IN CURSOR_1 LOOP
        V_SQL := V_SQL || ' SELECT ''' || C_TB.CM || ''' AS " "';
        FOR V_TB IN CURSOR_2 LOOP
          V_SQL := V_SQL || ',' || 'SUM(DECODE(A.ZZJG_ID,''' || V_TB.ZZJG_ID ||
                   ''', ' || C_TB.CM || ',0)) AS "' || V_TB.ZZJG_ID || '"';
        END LOOP;
        V_SQL := V_SQL || ' FROM TB_V2_DBINFO A ';
        V_SQL := V_SQL || ' UNION ALL';
      
      END LOOP;
      V_SQL   := SUBSTR(V_SQL, 1, LENGTH(V_SQL) - 10);
      V_COUNT := LENGTH(REPLACE(V_SQL, 'UNION ALL', ' '));
      V_BEGIN := 1;
      FOR I IN 1 .. V_COUNT LOOP
        V_END := INSTR(V_SQL, 'UNION ALL', 1, I);
        DBMS_OUTPUT.PUT_LINE(SUBSTR(V_SQL, V_BEGIN, V_END - V_BEGIN));
        V_BEGIN := V_END;
      END LOOP;
      DBMS_OUTPUT.PUT_LINE(SUBSTR(V_SQL, V_END, LENGTH(V_SQL) - V_END));
    END;结果运行至倒数第五行DBMS_OUTPUT.PUT_LINE(SUBSTR(V_SQL, V_BEGIN, V_END - V_BEGIN));这句时还是出现上述错误
      

  8.   

    上面的建表SQL应为：-- Create table
    create table TB_V2_DBINFO
    (
      ZZJG_ID NVARCHAR2(20) not null,
      MUSER   NVARCHAR2(60),
      M1      NVARCHAR2(50),
      M2      NVARCHAR2(50),
      M3      NVARCHAR2(50),
      M4      NVARCHAR2(50),
      M5      NVARCHAR2(50),
      M6      NVARCHAR2(50),
      M7      NVARCHAR2(50),
      M8      NVARCHAR2(50),
      M9      NVARCHAR2(50),
      M10     NVARCHAR2(50),
      M11     NVARCHAR2(50),
      M12     NVARCHAR2(50),
      M13     NVARCHAR2(50),
      M14     NVARCHAR2(50),
      M15     NVARCHAR2(50),
      M16     NVARCHAR2(50),
      M17     NVARCHAR2(50),
      M18     NVARCHAR2(50),
      M19     NVARCHAR2(50),
      M20     NVARCHAR2(50),
      M21     NVARCHAR2(50),
      M22     NVARCHAR2(50),
      M23     NVARCHAR2(50),
      M24     NVARCHAR2(50),
      M25     NVARCHAR2(50),
      M26     NVARCHAR2(50),
      M27     NVARCHAR2(50),
      M28     NVARCHAR2(50),
      M29     NVARCHAR2(50),
      M30     NVARCHAR2(50),
      M31     NVARCHAR2(50),
      M32     NVARCHAR2(50),
      M33     NVARCHAR2(50),
      M34     NVARCHAR2(50),
      M35     NVARCHAR2(50),
      M36     NVARCHAR2(50),
      M37     NVARCHAR2(50),
      M38     NVARCHAR2(50),
      M39     NVARCHAR2(50),
      M40     NVARCHAR2(50),
      M41     NVARCHAR2(50),
      M42     NVARCHAR2(50),
      M43     NVARCHAR2(50),
      M44     NVARCHAR2(50),
      M45     NVARCHAR2(50),
      M46     NVARCHAR2(50),
      M47     NVARCHAR2(50),
      M48     NVARCHAR2(50),
      M49     NVARCHAR2(50),
      M50     NVARCHAR2(50),
      M51     NVARCHAR2(50),
      M52     NVARCHAR2(50),
      M53     NVARCHAR2(50),
      M54     NVARCHAR2(50),
      M55     NVARCHAR2(50),
      M56     NVARCHAR2(50),
      M57     NVARCHAR2(50),
      M58     NVARCHAR2(50),
      M59     NVARCHAR2(50),
      M60     NVARCHAR2(50),
      M61     NVARCHAR2(50),
      M62     NVARCHAR2(50),
      M63     NVARCHAR2(50),
      M64     NVARCHAR2(50),
      M65     NVARCHAR2(50),
      M66     NVARCHAR2(50),
      M67     NVARCHAR2(50),
      M68     NVARCHAR2(50),
      M69     NVARCHAR2(50),
      M70     NVARCHAR2(50),
      M71     NVARCHAR2(50),
      M72     NVARCHAR2(50),
      M73     NVARCHAR2(50),
      M74     NVARCHAR2(50),
      M75     NVARCHAR2(50),
      M76     NVARCHAR2(50),
      M77     NVARCHAR2(50),
      M78     NVARCHAR2(50),
      M79     NVARCHAR2(50),
      M80     NVARCHAR2(50),
      UPTIME  DATE default SYSDATE
    )
      

  9.   

    V_COUNT := regexp_count(V_SQL,'UNION ALL');  查询UNION ALL出现的次数 要替换成其他方式的话  直接替换判断长度差好了V_COUNT := trunc((length(V_SQL)-length(replace(V_SQL,'UNION ALL','')))/9)