Query help

Transact-SQL
1

I need a t sql query which should list the parent with their associated childs and grandchildren to nth level

I need similare to the below oracle query which i was able to pull other dependent childrens and grand and greatgrand childrends with their parent..

select
SYS_CONNECT_BY_PATH(path), ' - '),level
FROM hierarchyrelation a INNER JOIN Person b ON b.personid = a.child
-- WHERE level <= 10
start with a.parentid in (select id from Person
where path like '%folder/file%')
CONNECT BY ROOT a.oid_nr = a.prn_oid_nr
AND LEVEL <= 10
order by SYS_CONNECT_BY_PATH(path), ' - ')

Thanks for your help in advance

2

is Oracle, This is a SQL Server forum

3

@gbritton, the OP is showing what they did in Oracle, but they want the T-SQL version.

4

I need a query which should pull all the records of parent and childrens in tsql which i was able pull in oracle.

6

Thanks here is the requested info....

CREATE TABLE #Treedesc
(
ID BIGINT NOT NULL,
Name Varchar(500) NOT NULL,
CONSTRAINT PK_Tree
PRIMARY KEY CLUSTERED ID)
);

INSERT INTO #Treedesc
(ID, Name)

SELECT 8571, 'File/sam' UNION ALL
SELECT 5475, 'Folder/Pat'UNION ALL
SELECT 6808, 'path/test'UNION ALL
SELECT 7591, 'file/test2'UNION ALL
SELECT 4485, 'Pr/dsn/'
;

CREATE TABLE #Tree
(
ParentID BIGINT NOT NULL,
ChildID INT NULL,
CONSTRAINT PK_Tree
PRIMARY KEY CLUSTERED ParentID,ChildID)
);

INSERT INTO #Tree
(ParentID, ChildID)

SELECT 8571, 5475 UNION ALL
SELECT 8571, 6808 UNION ALL
SELECT 8571, 7591 UNION ALL
SELECT 5475, 4485
;

WITH
cteHierarchy AS
(

--Get the top level

SELECT c.ChildID, a.ParentID, b.Name, Level = 1,
HierarchicalPath = CAST('-->'+CAST(Name AS VARCHAR(500)) AS VARCHAR(4000))
FROM #Tree a inner join #Treedesc b on a.ParentID = b.ID
inner join #Treedesc c on a.childID = c.ID

UNION ALL
--Get the recursive level
SELECT e.ChildID, e.ParentID, e.Name, Level = d.Level + 1,
HierarchicalPath = CAST(d.HierarchicalPath + '-->'+CAST(e.Name AS VARCHAR(500)) AS VARCHAR(4000))
FROM #Tree e
INNER JOIN cteHierarchy d ON e.ParentID = d.ChildID
)
SELECT ChildID, ,
ParentID ,
Name = SPACE((Level-1)*4) + Name,
Level,
HierarchicalPath
FROM cteHierarchy
ORDER BY HierarchicalPath
;
Expected output:

Parent child Parentchildtree

8571 5475 File/sam-->Folder/Pat
8571 6808 File/sam-->path/test
8571 7591 File/sam-->file/test2
5475 4485 File/sam-->Folder/Pat-->Pr/dsn/