SQL> select distinct
  2         ltrim(first_value(sys_connect_by_path(pk_corp,',')) over(order by level desc),',') as new_pk_corp
  3    from (
  4          select pk_corp,
  5                 row_number() over(order by pk_corp) as front_values,
  6                 (row_number() over(order by pk_corp))+1 as behind_values
  7            from tablename tt
  8           start with pk_corp = 1002
  9          connect by prior pk_corp = FATHERCORP
 10         )zz
 11  connect by prior behind_values = front_values;

NEW_PK_CORP
--------------------------------------------
1002,1003,1004,1005

------解决方案--------------------

SQL code
-- 树形数据深度排序处理示例(递归法)
--测试数据
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO

--广度搜索排序函数
CREATE FUNCTION f_Sort(@ID char(3)=NULL,@sort int=1)
RETURNS @t_Level TABLE(ID char(3),sort int)
AS
BEGIN
    DECLARE tb CURSOR LOCAL
    FOR
    SELECT ID FROM tb
    WHERE PID=@ID
        OR(@ID IS NULL AND PID IS NULL)
    OPEN TB
    FETCH tb INTO @ID
    WHILE @@FETCH_STATUS=0
    BEGIN
        INSERT @t_Level VALUES(@ID,@sort)
        SET @sort=@sort+1
        IF @@NESTLEVEL<32 --如果递归层数未超过32层(递归最大允许32层)
        BEGIN
            --递归查找当前节点的子节点
            INSERT @t_Level SELECT * FROM f_Sort(@ID,@sort)
            SET @sort=@sort+@@ROWCOUNT  --排序号加上子节点个数
        END
        FETCH tb INTO @ID
    END
    RETURN
END
GO

--显示结果
SELECT a.*
FROM tb a,f_Sort(DEFAULT,DEFAULT)