Dynamic hierarchical query in Teradata

Database
Highlighted
Fan

Dynamic hierarchical query in Teradata

Hi Team,

 

I am trying to achieve the following scenario in Teradata using Simple SQL query only.

Kindly advise.

 

Source Table(Example):

COL1     COL2

A             B

B             C

C             D

X             Y

Y             z

P             C

 

Expected Output (Kind of hierarchical results basis on input table data if output column number is fixed):

COL1      COL2       COL3      COL4

A              B              C            D

X              Y              Z             ?

P              C              D            ?

B              C              D            ?

C              D              ?             ?

D              ?               ?            ?

Y              Z               ?             ?

Z              ?               ?             ?

 

Thank you for your advise.

 

Best Regards


Accepted Solutions
Junior Contributor

Re: Dynamic hierarchical query in Teradata

For a known small number of columns simply utilize self joins:

SELECT t.col1, t1.col2, t2.col2, t3.col2
FROM -- base result set
 ( SELECT col1 FROM mytable
   UNION
   SELECT col2 FROM mytable
 ) AS t -- one join for each result column
LEFT JOIN mytable AS t1
  ON t1.col1 = t.col1
LEFT JOIN mytable AS t2
  ON t2.col1 = t1.col2
LEFT JOIN mytable AS t3
  ON t3.col1 = t2.col2

 

1 ACCEPTED SOLUTION
2 REPLIES
Junior Contributor

Re: Dynamic hierarchical query in Teradata

For a known small number of columns simply utilize self joins:

SELECT t.col1, t1.col2, t2.col2, t3.col2
FROM -- base result set
 ( SELECT col1 FROM mytable
   UNION
   SELECT col2 FROM mytable
 ) AS t -- one join for each result column
LEFT JOIN mytable AS t1
  ON t1.col1 = t.col1
LEFT JOIN mytable AS t2
  ON t2.col1 = t1.col2
LEFT JOIN mytable AS t3
  ON t3.col1 = t2.col2

 

Fan

Re: Dynamic hierarchical query in Teradata

Got it Dnoeth. Exactly what was needed.

Thanka a ton. You are great.

 

Regards,

Avishek