Recursive CTE SQL (Parent Child)

Question

I'm using SQL Server 2012

LOCATIONDETAIL table

OID     LOCATIONNAME    PARENTID
1           GLOBAL          0
2           NORTH           1
3           SOUTH           1
4           NORTH1          2
5           SOUTH1          3

LOCATIONSITECONFIG table

OID LOCATIONID  SITENAME
1       2        TEST

I use a recursive CTE Query

;WITH LOCALSITEHIERARCHY AS
(
    SELECT  A.OID
            ,A.PARENTOID
            ,CAST(A.LOCATIONNAME + ' ( ' + LSC.SITENAME + ' )' AS NVARCHAR(100)) AS NAME
            ,LSC.OID AS SITEOID
    FROM LOCATIONDETAIL A
            INNER JOIN LOCATIONSITECONFIG LSC 
                ON LSC.LOCATIONDETAILOID = A.OID                
    WHERE
            LSC.SITENAME <> 'GLOBAL' AND LSC.RECSTATUS = 'A'
    UNION ALL
    SELECT 
        A.OID
        ,A.PARENTOID
        ,CAST(A.LOCATIONNAME AS NVARCHAR(100))                  
        ,LH.SITEOID 
    FROM LOCATIONDETAIL A 
        INNER JOIN LOCALSITEHIERARCHY LH ON A.PARENTOID = LH.OID
)

SELECT * FROM LOCALSITEHIERARCHY

NORTH is a now a separate site in Global.This Query returns North and North 1 in tree structure which is OK.

When I make Global as a new site, i dont want to include North as part of Global site.

Example , adding site 2 named (NEWTEST), which is Global

LOCATIONSITECONFIG table

OID LOCATIONDETAILOID   SITENAME
1       2               TEST
2       1               NEWTEST

The above Query returns Global , NORTH, NORTH1, SOUTH, SOUTH1 and again NORTH(TEST) AND NORTH1 (Duplicate of records)

I want the query to return Global , NORTH (TEST), NORTH1, SOUTH, SOUTH1

The query should ignore if the child has site created already. Please help

Did you want to return GLOBAL (NEWTEST), NORTH (TEST), NORTH1, SOUTH, SOUTH1? — Koen, Aug 17 '17 at 09:08

Koen · Accepted Answer · 2017-08-21T09:47:20.930

By changing your CTE to begin with GLOBAL as anchor member. The recursive part needs to be adjusted to include the SITENAME, this is done by a coalesce (in case a location does not have a sitename) and a subquery.

with LOCALSITEHIERARCHY
     as (
     select A.OID
          , A.PARENTOID
          , cast(A.LOCATIONNAME+' ( '+LSC.SITENAME+' )' as nvarchar(100)) as NAME
          , LSC.OID as SITEOID
          , cast(row_number() over(partition by parentoid order by A.LOCATIONNAME) as varchar(max)) as [PATH]
     from LOCATIONDETAIL as A
          inner join LOCATIONSITECONFIG as LSC on LSC.LOCATIONDETAILOID = A.OID
     where 1 = 1
           and A.PARENTOID is null
           and LSC.RECSTATUS = 'A'
     union all
     select A.OID
          , A.PARENTOID
          , cast(coalesce(A.LOCATIONNAME+' ( '+
                         (
                             select SITENAME
                             from LOCATIONSITECONFIG C
                             where C.LOCATIONDETAILOID = A.OID
                         )+' )', A.LOCATIONNAME) as nvarchar(100)) as NAME
          , coalesce((select C.OID from LOCATIONSITECONFIG C where C.LOCATIONDETAILOID = A.OID),NULL) as SITEOID
          , [path]+'-'+cast(row_number() over(partition by A.parentoid order by A.LOCATIONNAME) as varchar(max))
     from LOCATIONDETAIL as A
          inner join LOCALSITEHIERARCHY as LH on A.PARENTOID = LH.OID)

     select * from LOCALSITEHIERARCHY order by [PATH];

The resulting output is shown in the picture below.

Thanks Koen, Well drafted answer. Let me try and get back to you. — Marid, Aug 20 '17 at 06:30
i tried the solution. The SiteOID should be displayed accordingly and here it is the last OID in the LOCATIONSITECONFIG is getting repeated. I want to have NORTH(Test) to have siteoid as 1 and Global (NewTEST) as 2. Could you please help with it. — Marid, Aug 21 '17 at 05:56
So you mean the fourth column SITEOID should be 1, 2, NULL, NULL, NULL? Only show the siteoid if the sitename exists for location? — Koen, Aug 21 '17 at 07:02

Recursive CTE SQL (Parent Child)

1 Answers1