title: SQL query to retrieve data which is in Parent and child relation in same table. Basically its a hierarchy table
body: <p>can some one help me with query to get data which is parent - child relation in same table.it is an Hierarchy table  </p>
  
  <p>Ex : Table</p>
  
  <pre><code>   Parent_ID | Parent_Name | Child_ID | Child_Name
       1       |    A        |   2      |    B
       2       |    B        |   3      |    C
       3       |    C        |   4      |    Cc
       3       |    C        |   5      |    Ccc
       4       |    Cc       |   6      |    C_C
       1       |    A        |   7      |    D
       7       |    D        |   8      |    Dd
       9       |    E        |   10     |    Ee
       10      |    Ee       |   11     |    Eee
  </code></pre>
  
  <p>Data Relation of above table is  </p>
  
  <pre><code>            For A -&gt; B is child
                       For  B -&gt; C is child
                             For  C -&gt; Cc and Ccc are Childs
                                       For Cc -&gt; C_C is child
  </code></pre>
  
  <p>want to retrieve data in following Table format :</p>
  
  <pre><code>    ParentId | Parent_Name | Child_Name | Child_Name | Child_Name | Child_Name
          1    |     A       |     B      |     C      |   Cc       |    Null
          1    |     A       |     B      |     C      |   Ccc      |    Null
          1    |     A       |     B      |     C      |   Cc       |    C_C
          1    |     A       |     D      |     Null   |   Null     |    Null
          1    |     A       |     D      |     Dd     |   Null     |    Null
          9    |     E       |     Ee     |     Null   |   Null     |    Null
          9    |     E       |     Ee     |     Eee    |   Null     |    Null
  </code></pre>
  
  <p>Another Example</p>
  
  <p>Geography</p>
  
  <pre><code>     Parent_ID | Parent_Name | Child_ID | Child_Name
         1       |    Asia     |   2      |    India
         2       |    India    |   3      |    SouthIndia
         3       |  SouthIndia |   4      |    karnataka
         3       |  SouthIndia |   5      |    Kerela
         4       |  karnataka  |   6      |    Bengaluru
         1       |    Asia     |   7      |    Bangladesh
         7       |  Bangladesh |   8      |    Dhaka
         9       |  Srilanka   |   10     |    Colombo
         10      |  Colombo    |   11     |    Colombo district
         12      |North America|   13     |    Canada
         13      | Canada      |   14     |    Alberta
         13      | Canada      |   15     |    Ontario
         15      | Ontario     |   16     |    Toronto
  </code></pre>
  
  <p>Expecting as below</p>
  
  <pre><code>Parent_ID|Continent    | Country    | Zone       | State    | City
  
     1     |   Asia      |  India     | SouthIndia | karnataka| Bengaluru
     1     |   Asia      |  India     | SouthIndia | Kerela   |   
     1     |   Asia      | Bangladesh |            | Dhaka    |   
     1     |   Asia      | Srilanka   |            | Colombo  |Colombo district
    12     |NorthAmerica | Canada     |            | Alberta  |    
    12     |NorthAmerica | Canada     |            | Ontario  | Toronto   
  </code></pre>
  
  <p>Thanks in Advance...</p>