title: IMPALA SQL (pivoting table)
body: <p>Here I’ve got the IMPALA SQL task, can someone help me to solve it please</p>
  
  <p>There is a table with three columns A B and C.
  There are three types of values: child in column A; child/parent in column A and C; and owner in column B.</p>
  
  <p>IF value appears in column C, then it is a parent value for other value in the same row  in the column A, and it also lives in column A a swell (as a child).</p>
  
  <p>There are multiple levels of children and parents (child for one value can be parent for another).</p>
  
  <p>All children and parent values are live together in column A;</p>
  
  <p>Owners are live only in column B;</p>
  
  <p>Children parent chain ends when owner appears (in column B).</p>
  
  <p>I need to pivot existing table in a way that reveals every chain in a separate row </p>
  
  <p>Child, parent-child 1, parent-child 2, parent-child 3, Owner.  </p>
  
  <p><a href="https://i.stack.imgur.com/tl7kW.png" rel="nofollow noreferrer">see pic_1</a></p>
  
  <p>I tried to solve this problem with LEFT JOINS </p>
  
  <pre><code>SELECT a.A, a.B, a.C,    b.A, b.B, b.C,   c.A, c.B, c.C
  FROM  
  table1 a 
  LEFT JOIN
  table1 b
  ON a.C = b.A
  LEFT JOIN
  table1 c 
  ON b.C = c.A
  </code></pre>
  
  <p>it works but it is a bad solution because we don’t know how many levels of ‘parent-child’ can be there. some chains are consist of only three parent/child
  values and some of them have 50 'chain-rings' prior to the owner  </p>