Pl / SQL-oracle 9i-手动透视

  SELECT   SUM(CASE WHEN age_range = \'5-10\' 
                    THEN number_of_people 
                    ELSE NULL END) AS nop5,
           SUM(CASE WHEN age_range = \'10-15\' 
                    THEN number_of_people 
                    ELSE NULL END) AS nop10
    FROM   customers
   WHERE   customer_name = 1;

SELECT deptno, 
       LTRIM(SYS_CONNECT_BY_PATH(ename,\',\'))
FROM   ( SELECT deptno,
                ename,
                ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS seq 
         FROM   emp ) 
WHERE  connect_by_isleaf = 1
CONNECT BY seq = PRIOR seq +1 AND deptno = PRIOR deptno 
START WITH seq = 1;


    DEPTNO CONCATENATED
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

CREATE OR REPLACE FUNCTION number_of_people(p_customer_name VARCHAR2)
RETURN VARCHAR2
IS
  v_number_of_people NUMBER;
  v_result VARCHAR2(500);

  CURSOR c1
  IS
    SELECT Number_of_people FROM the_table WHERE Customer_name = p_customer_name;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO v_number_of_people;
    EXIT WHEN c1%NOTFOUND;

    v_result := v_result || v_number_of_people || \' \' || CHR(13);
  END;
END;

SELECT number_of_people(1) INTO dual;

select Number_of_people
from table
where Customer_name = 1