Pl/SQL - oracle 9i -手动旋转

Question

我们有一个表，其中有三列：

Customer_name, Age_range, Number_of_people.
1              1-5         10
1              5-10        15

我们需要将不同年龄范围内的所有人数作为单个查询的行返回。如果我们搜索customer #1，查询应该只返回一行：

Header- Age Range (1-5)             Age Range (5-10)
        10                             15

我们需要获得一行中的所有结果；当我查询customer 1时，结果应该是group by age_range的单行中的人数。

实现这一目标的最佳方式是什么？

Answer 1

您需要手动执行透视：

  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;

Answer 2

有一些使用LISTGAGG、COLLECT或9i之后添加的其他功能的10g和11g的简单解决方案，但我相信以下内容将在9i中工作。

源(http://www.williamrobertson.net/documents/one-row.html)

您只需将deptno替换为customer_name，将ename替换为Number_of_people

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.

Answer 3

这将创建一个存储的FUNCTION，这意味着您可以随时访问它。

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;

希望这有帮助，如果有任何错误，请让我知道，我没有测试自己的函数。