SQL query for below scenario

Question

I have a processDependency table which has thousands of records. Sample records are given below:

Proc DepProc
P1   P2
P2   P3
P3   P4
P4   P5
P6   P7

So when I run the SQL query I should be able to derive below:

P1-P2-P3-P4-P5
P6-P7

Can anyone help me out with a generic SQL.

I do not believe that you can have a generic query for hierarchical processing with unlimited depth. You will need to use RDBMS-specific functionality. — PM 77-1, Apr 04 '16 at 18:29
So if we have a defined depth say for example 12.. Then is it possible? — Deepak Kumar, Apr 04 '16 at 18:31
is this a database that allows recursive CTEs? If so that is probably the way to do it. — Hogan, Apr 04 '16 at 18:34
See http://stackoverflow.com/questions/6401222/connect-by-or-hierarchical-queries-in-rdbms-other-than-oracle#comment7504770_6401301 — PM 77-1, Apr 04 '16 at 18:35
@Hogan it supports CTE.. Could you give a hint or a sample for me to start off.. — Deepak Kumar, Apr 04 '16 at 18:39
it can't be a select statement but a recursive function that can be called inline can produce that result. — Gubr, Apr 04 '16 at 18:48

score 1 · Accepted Answer · answered Apr 04 '16 at 19:54

This is not quite right because it gets all paths from Proc

But you did not make clear which one should be used first.

In any case it should give you an idea.

SELECT recurse AS
(
   SELECT Proc AS Start, 
          Proc||COALESCE('-'||DepProc,'') AS PATH, 
          DepProc as Next, 1 as Level
   FROM table

   UNION ALL

   SELECT Start,
          PATH||'-'||t.DepProc,
          t.DepProc as Next,
          Level+1 as Level
   FROM recurse r
   JOIN table t ON r.Next = t.Proc
), recurseWithMax AS
(
   SELECT PATH, Start, MAX(Level) OVER (Partition by Start) as Max, Level
)
SELECT Start, PATH
FROM recurseWithMax
WHERE Level = Max

Thanks a lot @Hogan, its working after tweaking the CTE query.. Your answer was really very much helpful. — Deepak Kumar, Apr 06 '16 at 13:40

SQL query for below scenario

1 Answers1