How to show recursive parent id in single column in SQL

Question

Here is an example table structure:

ID Name ParentID ----------------------- 1 Ancestor NULL 2 GrandFather 1 3 GrandMother 1 4 Child 3 

I am trying to write a query that will return

 ID Name Family ---------------------------- 1 Ancestor 2 GrandFather Ancestor 3 GrandMother Ancestor 4 Child Ancestor^GrandMother 

The tricky part is that I want to show the family of all rows and in order from top to bottom.

If someone can point me in the right direction, it would be helpful :)

EDIT :: This is a real request, but it follows the same idea. it returns an error in the line: marketparent.family + '^' + t2.marketGroupName because it cannot find the trademark

 WITH marketparent ( marketGroupID,parentGroupID, marketGroupName,family) AS ( SELECT marketGroupID, parentGroupID, marketGroupName, '' as family FROM EVE.dbo.invMarketGroups WHERE parentGroupID IS NULL UNION ALL SELECT t2.parentGroupID, t2.marketGroupID, t2.marketGroupName, marketparent.family + '^'+ t2.marketGroupName FROM EVE.dbo.invMarketGroups as t2 INNER JOIN marketparent as mp ON mp.marketGroupID = t2.parentGroupID ) -- Statement using the CTE SELECT TOP 10 * FROM marketparent; 

Answer 1

You did not specify your DBMS, so I assume PostgreSQL

 WITH RECURSIVE fam_tree (id, name, parent, family) as ( SELECT id, name, parentid, ''::text as family FROM the_unknown_table WHERE parent IS NULL UNION ALL SELECT t2.id, t2.name, t2.parentid, fam_tree.family || '^' || t2.name FROM the_unknown_table t2 INNER JOIN fam_tree ON fam_tree.id = t2.parentid ) SELECT * FROM fam_tree; 

This is standard SQL (with the exception of the ::text method), which should work with very small changes in most modern DBMSs.

Edit

For SQL Server, you need to replace the standard concatenation character with the non-standard Microsoft + character (and you need to remove the recursive keyword, which is required by the standard, but for some strange reason rejected by SQL Server)

 WITH fam_tree (id, name, parent, family) as ( SELECT id, name, parentid, '' as family FROM the_unknown_table WHERE parent IS NULL UNION ALL SELECT t2.id, t2.name, t2.parentid, fam_tree.family + '^' + t2.name FROM the_unknown_table t2 INNER JOIN fam_tree ON fam_tree.id = t2.parentid ) SELECT * FROM fam_tree; 

Answer 2

You can use the recursive expression Common Table Expression .

 declare @T table ( ID int, Name nvarchar(15), ParentID int ); insert into @T values (1, N'Ancestor', NULL), (2, N'GrandFather', 1), (3, N'GrandMother', 1), (4, N'Child', 3); with C as ( select T.ID, T.Name, T.ParentID, cast(N' ' as nvarchar(max)) as Family from @T as T where T.ParentID is null union all select T.ID, T.Name, T.ParentID, C.Family+'^'+C.Name from @T as T inner join C on T.ParentID = C.ID ) select C.ID, C.Name, stuff(C.Family, 1, 2, '') as Family from C; 

Answer 3

 select T.ID, T.Name, (select name from table where ID=T.ParentID)as Family from table T