Get hirarchy query

[Blue_Boy]

What did you so far for your query? Use CASE switch to archive your query.

---

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com

[SatyaKeerthi15]

I have thousands of records how can I use CASE ?

[J4amieC]

This should be some useful info for you. http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html[^]

[SatyaKeerthi15]

I have only two columns like below. id id_project id_parent ------------------------- 1 root root 2 abc123 root 3 xyz098 root 4 cmd003 xyz098 5 asd874 abc123 6 f8jk12 cmd003 So, I will need the output to look similar to this when pass id as '1' - abc123 -- asd874 - xyz098 -- cmd003 --- f8jk12 So, I will need the output to look similar to this when pass id as '3' - cmd003 -- f8jk12 So, I will need the output to look similar to this when pass id as '2' - asd874 How can I do this in sqlserver2005

[ScottM1]

I would use a cursor to step through each level of the hierarchy and insert the results into a temp table. Then just select from the temp table at the end.

[SatyaKeerthi15]

Can you post query how to step through each level

[ScottM1]

Actually, a recursive CTE is probably your best bet.

[ScottM1]

Here's an example that probably does exactly what you want: Recursive CTE[^]

[J4amieC]

Did you read the article i linked you to?

[SatyaKeerthi15]

I tried below stored procedure. I am getting the result what I want but its print statement. How can I convert this into table. ALTER PROC [dbo].[spa_R_TeamDetails] @ReportsTo INT AS BEGIN SET NOCOUNT ON DECLARE @PersonId INT, @DisplayName VARCHAR(100) SET @DisplayName = (SELECT DisplayName+'~'+CONVERT(VARCHAR,PersonId)+'~'+OrgEmpId TeamMembers FROM Person WHERE PersonID = @ReportsTo) PRINT REPLICATE('- ', (@@NESTLEVEL * 1)-1) + @DisplayName SET @PersonId = (SELECT MIN(PersonId) FROM Person WHERE ReportsTo = @ReportsTo) WHILE @PersonId IS NOT NULL BEGIN EXEC spa_R_TeamDetails @PersonId SET @PersonId = (SELECT MIN(PersonId) FROM Person WHERE ReportsTo = @ReportsTo AND PersonId > @PersonId) END END -- spa_R_TeamDetails 1781

[SatyaKeerthi15]

This is the sp I have tried. I am getting what I want but in print statement. How to convert it into Table. ALTER PROC [dbo].[spa_R_TeamDetails] @ReportsTo INT AS BEGIN SET NOCOUNT ON DECLARE @PersonId INT, @DisplayName VARCHAR(100) SET @DisplayName = (SELECT DisplayName+'~'+CONVERT(VARCHAR,PersonId)+'~'+OrgEmpId TeamMembers FROM Person WHERE PersonID = @ReportsTo) PRINT REPLICATE('- ', (@@NESTLEVEL * 1)-1) + @DisplayName SET @PersonId = (SELECT MIN(PersonId) FROM Person WHERE ReportsTo = @ReportsTo) WHILE @PersonId IS NOT NULL BEGIN EXEC spa_R_TeamDetails @PersonId SET @PersonId = (SELECT MIN(PersonId) FROM Person WHERE ReportsTo = @ReportsTo AND PersonId > @PersonId) END END -- spa_R_TeamDetails 1781

[SatyaKeerthi15]

This is the sp I have tried. I am getting what I want but in print statement. How to convert it into Table. ALTER PROC [dbo].[spa_R_TeamDetails] @ReportsTo INT AS BEGIN SET NOCOUNT ON DECLARE @PersonId INT, @DisplayName VARCHAR(100) SET @DisplayName = (SELECT DisplayName+'~'+CONVERT(VARCHAR,PersonId)+'~'+OrgEmpId TeamMembers FROM Person WHERE PersonID = @ReportsTo) PRINT REPLICATE('- ', (@@NESTLEVEL * 1)-1) + @DisplayName SET @PersonId = (SELECT MIN(PersonId) FROM Person WHERE ReportsTo = @ReportsTo) WHILE @PersonId IS NOT NULL BEGIN EXEC spa_R_TeamDetails @PersonId SET @PersonId = (SELECT MIN(PersonId) FROM Person WHERE ReportsTo = @ReportsTo AND PersonId > @PersonId) END END -- spa_R_TeamDetails 1781

[ScottM1]

Insert it into a temp table and then select * from that table at the end.

[SatyaKeerthi15]

I did that ... As everytime sp excutes output displays like multiple tables but not in one table

[ScottM1]

Have you tried using a CTE or a cursor? I reckon those are your best bet and probably what your teacher is looking for if this is a homework question.

[jayantbramhankar]

To get result, you need to write recursive function A function to get Parent

CREATE FUNCTION dbo.FindRoot(@id int)
RETURNS int
AS
BEGIN
DECLARE @Id_parent int
SELECT @Id_parent= Id_parent
FROM TableName
WHERE id = @id
WHILE @Id_parent <> NULL
BEGIN
SELECT @id = @Id_parent
SELECT @Id_parent = Id_parent
FROM PrimeInfo
WHERE id = @id
END
RETURN @id
END

and then store procedure to extract your data by providing id value

CREATE PROCEDURE BuildTree(@id int)
AS
SET NOCOUNT ON
CREATE TABLE #results(level int, id int, id_parent int)
DECLARE @id_parent int
DECLARE @level int
SELECT @level = 1
DECLARE @root int
SELECT @root = dbo.FindRoots(@id)
CREATE TABLE #stack (id int, level smallint)
INSERT INTO #stack VALUES (@root, @level)
WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE level = @level)
BEGIN
SELECT @id = s.id, @id_parent= IsNull(t.id_parent, 0)
FROM #stack s INNER JOIN TableName t
ON t.id = s.id
WHERE level = @level
INSERT INTO #results VALUES (@level, @id, @id_parent)
DELETE FROM #stack
WHERE level = @level
AND id = @id
INSERT #stack
SELECT id, @level + 1
FROM TableName
WHERE id_parent = @id
IF @@ROWCOUNT > 0
BEGIN
SELECT @level = @level + 1
END
END--IF EXISTS
ELSE
BEGIN
SELECT @level = @level - 1
END
END -- WHILE
SELECT id, id_parent, level FROM #results

[ps_prakash02]

Have a look to this code and see whether it solves your problem, CREATE TABLE TempTree (Id int IDENTITY, Id_Project VARCHAR(100), Id_Parent VARCHAR(100)) INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Root','Root') INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 1 1', 'Root') INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 1 2', 'Root') INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 1 3', 'Root') INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 2 1', 'Level - 1 1') INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 2 2', 'Level - 1 1') INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 2 3', 'Level - 1 2') INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 2 4', 'Level - 1 2') INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 2 5', 'Level - 1 3') INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 2 6', 'Level - 1 3') INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 1', 'Level - 2 1') INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 2', 'Level - 2 1') INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 3', 'Level - 2 2') INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 4', 'Level - 2 2') INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 5', 'Level - 2 3') INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 6', 'Level - 2 3') INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 7', 'Level - 2 4') INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 8', 'Level - 2 4') INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 9', 'Level - 2 5') INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 10', 'Level - 2 5') INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 11', 'Level - 2 6') INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 12', 'Level - 2 6') CREATE PROC Dbo.Proc_TheTree (@parent VARCHAR(100)) AS CREATE TABLE #TheList (RootId int, RootName VARCHAR(100), ChildName VARCHAR(100)) CREATE TABLE #TheSearch (SLNO INT IDENTITY, ParentName VARCHAR(100), IsSearchCompleted BIT) IF NOT EXISTS (SELECT * FROM TempTree WHERE id_parent = @parent) BEGIN SELECT * FROM TempTree WHERE id_project = @parent END ELSE BEGIN INSERT INTO #TheSearch (ParentName, IsSearchCompleted) SELECT id_project, 0 FROM TempTree WHERE id_parent = @parent INSERT INTO #TheList (RootId, RootName, ChildName) SELECT (SELECT Id FROM Temp