title: transforming hierarchy table into bridge
body: <p>I have this original table:</p>
  
  <pre><code>IF OBJECT_ID('tempdb..#Hierarchies') IS NOT NULL DROP TABLE #Hierarchies
  
  CREATE TABLE #Hierarchies
      (
        [HierarchyId] [INT] NOT NULL ,
        [HierarchyName] NVARCHAR(MAX),
        [ParentHierarchyId] [INT]
          CONSTRAINT [PK_Hierarchies]
          PRIMARY KEY CLUSTERED ( [HierarchyId] ASC )
          WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
                 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
                 ALLOW_PAGE_LOCKS = ON )
      )
  ON  [PRIMARY]
  
  INSERT INTO #Hierarchies ([HierarchyId], [HierarchyName], [ParentHierarchyId])
  SELECT 1 , 'A', NULL
  UNION ALL
  SELECT 2 , 'B', 1
  UNION ALL
  SELECT 3 , 'C', 1
  UNION ALL
  SELECT 4 , 'D', 2
  
  SELECT * FROM #Hierarchies
  
  SELECT 
      b.HierarchyId,
      a.HierarchyId
  FROM #Hierarchies a
  CROSS JOIN
   #Hierarchies b
   ORDER BY b.HierarchyId
  </code></pre>
  
  <p>and would like to transform it into this format:</p>
  
  <pre><code>ParentId    ChildId LevelsRemoved
  1   1   0
  1   2   1
  1   3   1
  1   4   2
  2   2   0
  2   4   1
  3   3   0
  </code></pre>
  
  <p>Before I try to implement the wheel, I was wondering if someone could be so kind and point me to an existing sql script for this? Thanks.</p>