Pl/SQL - oracle 9i - Manual Pivoting

We have a table which has three columns in it:

Customer_name, Age_range, Number_of_people.
1         开发者_如何学Go     1-5         10
1              5-10        15

We need to return all the number of people in different age ranges as rows of a single query. If we search for customer #1, the query should just return one row:

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

We needed to get all the results in a single row; When I query for customer 1, the result should be only number of people in a single row group by age_range.

What would be the best way to approach this?

You need to manually perform a pivot:

  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;

There are easy solutions with 10g and 11g using LISTGAGG, COLLECT, or other capabilities added after 9i but I believe that the following will work in 9i.
Source (http://www.williamrobertson.net/documents/one-row.html)

You will just need to replace deptno with customer_name and ename with 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.

This will create a stored FUNCTION which means you can access it at any time.

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;

To run it, use:

SELECT number_of_people(1) INTO dual;

Hope this helps, and please let me know if there are any errors, I didn't testrun the function myself.

Just do

select Number_of_people
from table
where Customer_name = 1

Are we missing some detail?