Oracle 行和列轉換

 

1.表結構如下:

 

create table JAY_TEST_0912
(
  NAME   VARCHAR2(20),
  LENGTH NUMBER,
  WIDTH  NUMBER,
  HIGH   NUMBER
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

 

2.數據如下:

NAME   LENGTH     WIDTH HIGH

AAA           10                 20         30 ;

 

3.創建一TYPE

CREATE OR REPLACE TYPE "TOOLS_STRLIST" ;

 

4.創建一Function

create or replace function Tools_Str2List(str_in in varchar2) return Tools_StrList  is
 v_Str   Long Default Str_In || ',';
 v_Index Number;
 v_List  Tools_StrList := Tools_StrList();
Begin
 Loop
  v_Index := Instr(v_Str, ',');
  Exit When(Nvl(v_Index, 0) = 0);
  v_List.Extend;
  v_List(v_List.Count) := Trim(Substr(v_Str, 1, v_Index - 1));
  v_Str := Substr(v_Str, v_Index + 1);
 End Loop;
 Return v_List;
End;

 

5.將結果用逗號隔開
  FUNCTION F_GETFILTER(N NUMBER) RETURN VARCHAR2
  IS
    V_STR    VARCHAR2(100):='';
    V_LENGTH NUMBER;
    V_WIDTH  NUMBER;
    V_HIGH   NUMBER;
    O_CUR      MYTYPE;
  BEGIN
    IF (N = 1) THEN
      OPEN  O_CUR FOR
        SELECT  A.LENGTH,
                A.WIDTH,
                A.HIGH

        FROM DUAL LEFT JOIN
          (SELECT * FROM JAY_TEST_0912) A ON 1=1;
      LOOP
      FETCH O_CUR INTO V_LENGTH,V_WIDTH,V_HIGH;
      EXIT WHEN O_CUR%NOTFOUND;     
        V_STR := V_LENGTH||','||V_WIDTH||','||V_HIGH;
      END LOOP;    
    END IF;
    RETURN V_STR;
  END;

 

6.行和列轉換

PROCEDURE P_UANDD(CUR OUT MYTYPE)
  AS
  BEGIN
    OPEN CUR FOR
    SELECT A.NAME1,A.NAME2,C.STR
    FROM
    (
      SELECT ROWNUM R1,A.NAME1,B.NAME2
      FROM
      (
      SELECT NAME NAME1
      FROM  JAY_TEST_0912
      ) A LEFT JOIN
      (
      SELECT DECODE(COLUMN_NAME,COLUMN_NAME,COLUMN_NAME) NAME2
             --DECODE(COLUMN_NAME,'LENGTH','LENGTH','WIDTH','WIDTH','HIGH','HIGH') VALUE
      FROM USER_TAB_COLUMNS U
      WHERE U.TABLE_NAME = 'JAY_TEST_0912'
      AND   U.COLUMN_NAME <> 'NAME'
      ) B     
      ON 1=1
     ) A,
     (SELECT ROWNUM R2,COLUMN_VALUE STR FROM  TABLE(NGPS.TOOLS_STR2LIST(PKG_JAY_TEST.F_GETFILTER(1))))  C
    WHERE A.R1 = C.R2;
  END P_UANDD;