Hierarchical table merge itself many times

Question

   CREATE TABLE b( ID         VARCHAR2(50 BYTE),
                   PARENT_ID  VARCHAR2(50 BYTE),
                   NAME       NVARCHAR2(200)
     );

https://community.oracle.com/thread/3513540?

Above link i explained everything

Answer 1

Any reason you don't like the answer posted there? Does the SQL Fiddle which illustrates the answer in question not as you've described your needed results to be?

Edit:

An SQL Fiddle using the technique Frank Kulash suggested. This is the first Oracle based query I have written since 2002 ( on 9iAS ).

SELECT  REGEXP_SUBSTR( a.PIDPath, '[^,]+', 1, 1 )    Header1,
        REGEXP_SUBSTR( a.IDPath, '[^,]+', 1, 1 )     Header2,
        REGEXP_SUBSTR( a.NamePath, '[^,]+', 1, 1 )   Header3,

        REGEXP_SUBSTR( a.PIDPath, '[^,]+', 1, 2 )    Header4,
        REGEXP_SUBSTR( a.IDPath, '[^,]+', 1, 2 )     Header5,
        REGEXP_SUBSTR( a.NamePath, '[^,]+', 1, 2 )   Header6,

        REGEXP_SUBSTR( a.PIDPath, '[^,]+', 1, 3 )    Header7,
        REGEXP_SUBSTR( a.IDPath, '[^,]+', 1, 3 )     Header8,
        REGEXP_SUBSTR( a.NamePath, '[^,]+', 1, 3 )   Header9,

        REGEXP_SUBSTR( a.PIDPath, '[^,]+', 1, 4 )    Header10,
        REGEXP_SUBSTR( a.IDPath, '[^,]+', 1, 4 )     Header11,
        REGEXP_SUBSTR( a.NamePath, '[^,]+', 1, 4 )   Header12,

        REGEXP_SUBSTR( a.PIDPath, '[^,]+', 1, 5 )    Header13,
        REGEXP_SUBSTR( a.IDPath, '[^,]+', 1, 5 )     Header14,
        REGEXP_SUBSTR( a.NamePath, '[^,]+', 1, 5 )   Header15

        --,REGEXP_SUBSTR( a.PIDPath, '[^,]+', 1, n )    
        --,REGEXP_SUBSTR( a.IDPath, '[^,]+', 1, n )     
        --,REGEXP_SUBSTR( a.NamePath, '[^,]+', 1, n )

FROM (  SELECT  LEVEL                                RootLvl,
                b.ID                                 RootID,
                SYS_CONNECT_BY_PATH( b.PARENT_ID, ',' )
                    || ','                           PIDPath,
                SYS_CONNECT_BY_PATH( b.ID, ',' )        
                    || ','                           IDPath,
                SYS_CONNECT_BY_PATH( b.NAME, ',' )                       
                    || ','                           NamePath
        FROM    t b
        START WITH b.PARENT_ID = '1'
        CONNECT BY NOCYCLE PRIOR b.ID = b.PARENT_ID ) a
ORDER BY a.RootLvl, a.RootID;

Frank Kulash points out:

The number of columns in the result set has to be hard-coded into the query. You can code something today that produces 30 columns (that is, enough for 10 levels in the hierarchy), but if you change the data later so that there are 11 or more levels, then your query will start to lose results.

I may be back to try a PIVOT thing later. Oracle is craziness.

Edit:

But manageable enough. A static PIVOT works just fine. I can't get CROSS APPLY working like I think it should with VALUES ( MSSQL style ), so I've given up and substituted it for a passable UNION ALL. There's potential for some dynamic SQL work with this one, so it actually might be able to do what you need without hard coding the columns.

;
WITH c ( RID, ID, PARENT_ID, NAME ) AS (
    SELECT  ROW_NUMBER() OVER (
            ORDER BY PARENT_ID )        RID,
            ID, PARENT_ID, NAME
    FROM    t
    UNION ALL
    SELECT  b.RID, a.ID, a.PARENT_ID, a.NAME
    FROM    t a,
            c b
    WHERE   a.ID = b.PARENT_ID
)
SELECT  p."'ID_1'"                              Header1,
        p."'PARENT_ID_1'"                       Header2,
        p."'NAME_1'"                            Header3,
        p."'ID_2'"                              Header4,
        p."'PARENT_ID_2'"                       Header5,
        p."'NAME_2'"                            Header6,
        p."'ID_3'"                              Header7,
        p."'PARENT_ID_3'"                       Header8,
        p."'NAME_3'"                            Header9,
        p."'ID_4'"                              Header10,
        p."'PARENT_ID_4'"                       Header11,
        p."'NAME_4'"                            Header12,
        p."'ID_5'"                              Header13,
        p."'PARENT_ID_5'"                       Header14,
        p."'NAME_5'"                            Header15
FROM (  SELECT  RID, 
                'ID_' || ROW_NUMBER() OVER (
                    PARTITION BY RID
                    ORDER BY ID )               KeyName,
                ID                              KeyValue
        FROM    c
        UNION ALL
        SELECT  RID, 
                'PARENT_ID_' || ROW_NUMBER() OVER (
                    PARTITION BY RID
                    ORDER BY ID )               KeyName,
                PARENT_ID                       KeyValue
        FROM    c
        UNION ALL
        SELECT  RID, 
                'NAME_' || ROW_NUMBER() OVER (
                    PARTITION BY RID
                    ORDER BY ID )               KeyName,
                CAST( NAME AS VARCHAR2( 200 ) ) KeyValue
        FROM    c ) s
PIVOT ( MAX( KeyValue ) FOR KeyName IN (
            'ID_1', 'PARENT_ID_1', 'NAME_1',
            'ID_2', 'PARENT_ID_2', 'NAME_2',
            'ID_3', 'PARENT_ID_3', 'NAME_3',
            'ID_4', 'PARENT_ID_4', 'NAME_4',
            'ID_5', 'PARENT_ID_5', 'NAME_5' ) ) p
ORDER BY p.RID;