Change select with connect by prior from Oracle to SQL Server

Question

Hello I have this part of a view in an Oracle database and I must change it on Microsoft Sql Server.

with V_LOCHIERARHY_N
(nr, nivel, location, parent, systemid, siteid, orgid, count_a, count_wo, children)
AS
SELECT     LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || l.LOCATION nivel,
              LOCATION, PARENT, systemid, siteid, orgid,
             (SELECT COUNT (a.ancestor)
             FROM locancestor a
            WHERE a.LOCATION = l.LOCATION AND a.siteid = l.siteid),
                  NVL (COUNT (w.wonum), 0)
             FROM maximo.workorder w

            WHERE  (    w.reportdate >
                          TO_TIMESTAMP ('2006-06-19 00:00:01',
                                        'YYYY-MM-DD HH24:MI:SS.FF'
                                       )
                   AND w.istask = 0
                   AND w.worktype <> 'P'
                   AND w.LOCATION = l.LOCATION
                  )
              AND w.status <> 'CAN'),
          l.children
     FROM lochierarchy l
    START WITH l.LOCATION = 'StartPoint'
   CONNECT BY PRIOR l.LOCATION = l.PARENT AND l.siteid = 'SiteTest'

What I need from this script is to return all the children of a given entry (the description of the children which can be found in locations table).

I have a table with next columns:

Location Parent      Systemid Children Siteid Origid Lochierarchyid
A001     StartPoint  Primary  2        SiteTest    X      106372
A002     A001        Primary  2        SiteTest    X      105472
A003     A002        Primary  0        SiteTest    X      98654
A004     A002        Primary  1        SiteTest    X      875543
A004B    A004        Primary  0        SiteTest    X      443216
B005     StartPoint  Primary  0        SiteTest    X      544321

For example for given entry A001 will return

---

A002    
 A003     
 A004
  A004B     
B005 

I have made this view below but I don't know how to integrate it with the first one. Also it doesn't return me the list in the corectly order

Parent  
 Children 1 of parent
  Children a of children 1
  children b of children 1
 children 2 of parent
  children a1 of children 2 and so on.

---

 WITH testCTE AS
(
     SELECT l.parent, l.location as child, l.location, l.lochierarchyid
     FROM lochierarchy l
     where location='SecondLocation' --and siteid='SiteTest'
       UNION ALL
     SELECT c.Parent, l.parent, l.location, l.lochierarchyid
     FROM lochierarchy l 
    INNER JOIN testCTE c ON l.parent = c.location
 )
  SELECT  *
  FROM testCTE c
  order BY c.parent,child asc
 ;

Can please someone help me? :)

Answer 1

Following the query proposed by mathguy, modified for MSSQL (2012)

    with
         inputs ( location, parent ) as (
           select 'A001' , 'StartPoint' union all
           select 'A002' , 'A001'       union all
           select 'A003' , 'A002'       union all
           select 'A004' , 'A002'       union all
       select 'A004B', 'A004'       union all
       select 'B005' , 'StartPoint' 
     ),
     r (lvl, location, ord ) as (
       select  1, location, CAST(location AS VARCHAR(400))
         from  inputs
         where parent = 'StartPoint'
       union all
       select  r.lvl + 1, i.location, CAST(r.location + '/' + i.location AS VARCHAR(400))
         from  r join inputs i on r.location = i.parent
     )
select REPLICATE(' ', 2 * (lvl-1)) + location as location
from   r
order by ord
;

Ouput:

location
-------------------------------------------------------------------
A001
  A002
    A003
    A004
      A004B
B005

Answer 2

Here is how you can do this (in Oracle, the only flavor I know) using a recursive query. "The web" reports SQL Server implements recursive queries as well, and with the same syntax (I believe all of this is SQL Standard compliant, so that's not surprising). Give it a try.

Instead of creating a table, I put all the test data in the first CTE. When you try this solution, delete the CTE named inputs first, and use your actual table name in the rest of the query.

with
     inputs ( location, parent ) as (
       select 'A001' , 'Downstream' from dual union all
       select 'A002' , 'A001'       from dual union all
       select 'A003' , 'A002'       from dual union all
       select 'A004' , 'A002'       from dual union all
       select 'A004B', 'A004'       from dual union all
       select 'B005' , 'Downstream' from dual
     ),
     r ( lvl, location ) as (
       select  1, location
         from  inputs
         where parent = 'Downstream'
       union all
       select  r.lvl + 1, i.location
         from  r join inputs i on r.location = i.parent
     )
     search depth first by lvl set ord
select lpad(' ', 2 * (lvl-1), ' ') || location as location
from   r
order by ord
;


LOCATION
--------------------
A001
  A002
    A003
    A004
      A004B
B005

6 rows selected.

ADDED: It seems SQL Server doesn't have the search depth/breadth first clause for recursive CTE's (or perhaps the syntax is different). In any case, here is a primitive "manual" implementation of the same:

with  (   .........   ),
     r ( lvl, location, ord ) as (
       select  1, location, location
         from  inputs
         where parent = 'Downstream'
       union all
       select  r.lvl + 1, i.location, r.location || '/' || i.location
         from  r join inputs i on r.location = i.parent
     )
select lpad(' ', 2 * (lvl-1), ' ') || location as location
from   r
order by ord
;