PL/SQL 菜鸟提问 怎么把相同的人的记录放到同一行中，航班号用逗号隔开

Question

SQL> declare
  2  
  3  cursor cur_2 is
  4  select passenger.familyname,passenger.givenname,passenger.passengerID,flew.flightid
  5  from passenger,flew
  6  where passenger.passengerid=flew.passengerid
  7  order by familyname,givenname;
  8  begin
  9  for e in cur_2 loop
 10  dbms_output.put_line('Name:'||e.familyname||','||e.givenname||' '||'PassengerID:'||e.passengerID||' '||e.flightID);
 11  end loop;
 12  close cur_2;
 13  end;
 14  /下面是部分结果
Name:Adams,Barbara PassengerID:pass014    AA122                                                     
Name:Adams,Barbara PassengerID:pass014    SW117                                                     
Name:Adams,Barbara PassengerID:pass014    SW292                                                     
Name:Adams,Barbara PassengerID:pass014    AA101                                                     
Name:Adams,Barbara PassengerID:pass014    AA101                                                     
Name:Adams,Brian PassengerID:pass038    AA106                                                       
Name:Adams,Brian PassengerID:pass038    SW139                                                       
Name:Adams,Brian PassengerID:pass038    AA101                                                       
Name:Adams,Brian PassengerID:pass038    US105                                                       
Name:Adams,Brian PassengerID:pass038    SW103                                                       
Name:Adams,Cameron PassengerID:pass076    AA101                                                     
Name:Adams,Carol PassengerID:pass060    SW109                                                       
Name:Adams,Carol PassengerID:pass060    SW109                                                       
Name:Adams,Carol PassengerID:pass060    UA118                                                       
Name:Adams,Deborah PassengerID:pass143    AA110                                                     
Name:Adams,Deborah PassengerID:pass143    AA106                                                     
Name:Adams,Deborah PassengerID:pass143    US105                                                     
Name:Adams,Deborah PassengerID:pass143    NW145                                                     
Name:Adams,Donna PassengerID:pass174    SW130                                                       
Name:Adams,Donna PassengerID:pass174    US105                                                       
Name:Adams,Donna PassengerID:pass174    SW117                                                       
Name:Adams,Dorothy PassengerID:pass187    AA110                                                     
Name:Adams,Douglas PassengerID:pass158    AA101                                                     
Name:Adams,Douglas PassengerID:pass158    DL104                                                     
Name:Adams,Douglas PassengerID:pass158    SW130 
同一个人的记录分成了好几行
怎么把同一个人的记录放到同一行里？
航班号还要用逗号分隔开要用两个游标做么？
请大家帮帮忙改改阿==
急啊！在线等！

Answer 1

1、可以使用一个语句直接实现
SELECT passenger.familyname || ',' || passenger.givenname,
       passenger.passengerID || ' ' || wm_concat(flew.flightid)
  FROM passenger, flew
 WHERE passenger.passengerid = flew.passengerid
 ORDER BY familyname, givenname;
SELECT passenger.familyname,
       passenger.givenname,
       passenger.passengerID,
       flew.flightid
  FROM passenger, flew
 WHERE passenger.passengerid = flew.passengerid
 GROUP BY familyname, givenname, passenger.passengerID
 ORDER BY familyname, givenname;
2、修改一下PL/SQL块也可以
--自己测试一下
DECLARE
  CURSOR cur_2 IS
    SELECT passenger.familyname,
           passenger.givenname,
           passenger.passengerID,
           flew.flightid
      FROM passenger, flew
     WHERE passenger.passengerid = flew.passengerid
     ORDER BY familyname, givenname;
  v_familyname  passenger.familyname%TYPE;
  v_givenname   passenger.givenname%TYPE;
  v_passengerID passenger.passengerid%TYPE;
  v_flightId    VARCHAR2(10000);
BEGIN
  FOR e IN cur_2 LOOP
  
    IF v_familyname = e.familyname AND v_givenname = e.givenname AND
       v_passengerID = e.passengerID THEN
      v_flightId := v_flightId || ',' || e.flightid;
    
    ELSE
      IF v_familyname IS NOT NULL THEN
        dbms_output.put_line('Name:' || v_familyname || ',' || v_givenname || ' ' ||
                             'PassengerID:' || v_passengerID || ' ' ||
                             ltrim(v_flightId, ','));
      END IF;
      v_familyname  := e.familyname;
      v_givenname   := e.givenname;
      v_passengerID := e.passengerID;
      v_flightId    := '';
    END IF;
  
  END LOOP;
  CLOSE cur_2;
END;
/

Answer 2

DECLARE
  TYPE RS_T IS TABLE OF VARCHAR2(2000) INDEX BY VARCHAR2 (50) ;
  RS RS_T ;
  IDX VARCHAR2(50) ;
  CURSOR CUR_2 IS
    SELECT PASSENGER.FAMILYNAME,
           PASSENGER.GIVENNAME,
           PASSENGER.PASSENGERID,
           FLEW.FLIGHTID
      FROM PASSENGER, FLEW
     WHERE PASSENGER.PASSENGERID = FLEW.PASSENGERID
     ORDER BY FAMILYNAME, GIVENNAME;
BEGIN
  FOR E IN CUR_2 LOOP
    BEGIN
      IF RS(E.FAMILYNAME) IS NOT NULL AND E.FAMILYNAME IS NOT NULL THEN
        RS(E.FAMILYNAME) :=  RS(E.FAMILYNAME) || ',' || E.FLIGHTID ;
      END IF ;
    EXCEPTION 
        WHEN OTHERS THEN
      RS(E.FAMILYNAME) := 'Name:' 
                       || E.FAMILYNAME || ',' 
                       || E.GIVENNAME || ' ' 
                       || 'PassengerID:' 
                       || E.PASSENGERID 
                       || ' ' 
                       || E.FLIGHTID ;
    END ;
  END LOOP;
  WHILE NVL(IDX,'1') <> RS.LAST LOOP
    IF IDX IS  NULL THEN
      IDX := RS.FIRST ;
    ELSE
      IDX := RS.NEXT(IDX) ;
    END IF ;
  END LOOP ;
  CLOSE CUR_2;
END;
/

Answer 3

上面的错了。DECLARE
  TYPE RS_T IS TABLE OF VARCHAR2(2000) INDEX BY VARCHAR2 (50) ;
  RS RS_T ;
  IDX VARCHAR2(50) ;
  CURSOR CUR_2 IS
    SELECT PASSENGER.FAMILYNAME,
           PASSENGER.GIVENNAME,
           PASSENGER.PASSENGERID,
           FLEW.FLIGHTID
      FROM PASSENGER, FLEW
     WHERE PASSENGER.PASSENGERID = FLEW.PASSENGERID
     ORDER BY FAMILYNAME, GIVENNAME;
BEGIN
  FOR E IN CUR_2 LOOP
    BEGIN
      IF RS(E.FAMILYNAME) IS NOT NULL AND E.FAMILYNAME IS NOT NULL THEN
        RS(E.FAMILYNAME) :=  RS(E.FAMILYNAME) || ',' || E.FLIGHTID ;
      END IF ;
    EXCEPTION 
        WHEN OTHERS THEN
      RS(E.FAMILYNAME) := 'Name:' 
                       || E.FAMILYNAME || ',' 
                       || E.GIVENNAME || ' ' 
                       || 'PassengerID:' 
                       || E.PASSENGERID 
                       || ' ' 
                       || E.FLIGHTID ;
    END ;
  END LOOP;
DBMS_OUTPUT.PUT_LINE(RS.COUNT) ;
  WHILE NVL(IDX,'1') <> RS.LAST LOOP
    IF IDX IS  NULL THEN
      IDX := RS.FIRST ;
    ELSE
      IDX := RS.NEXT(IDX) ;
    END IF ;
DBMS_OUTPUT.PUT_LINE(RS(IDX)) ;
  END LOOP ;
  CLOSE CUR_2;
END;
/