SQL - get all parents/childs?

Question

hopefully someone can help with this. I have recieved a table of data which I need to restructure and build a Denorm table out of. The table structure is as follows

UserID     Logon    ParentID 
2344       Test1     2000
2345       Test2     2000

The issue I have is the ParentID is also a UserID of its own and in the same table.

SELECT * FROM tbl where ParentID=2000 gives the below output

UserID     Logon      ParentID 
2000       Test Team     2500

Again, the ParentID of this is also stored as a UserID..

SELECT * FROM tbl where ParentID=2500 gives the below output

UserID     Logon            ParentID 
2500       Test Division     NULL

I want a query that will pull all of these relationships and the logons into one row, with my output looking like the below.

 UserID   Username       Parent1        Parent2        Parent3     Parent4
 2344       Test1      Test Team      Test Division    NULL         NULL
 2345       Test2      Test Team      Test Division    NULL         NULL

The maximum number of parents a user can have is 4, in this case there is only 2. Can someone help me with the query needed to build this?

Appreciate any help Thanks Jess

Please show us what you have tried, and why its not working. — Dave C, Nov 22 '18 at 15:13
One solution I found had the parents and childIDs on separate table which isn't the case here. i wouldn't know where to start with the above as I am not too advanced — Jess8766, Nov 22 '18 at 15:15
Have a look at recursive cte. An application is in this link: https://stackoverflow.com/questions/959804/simulation-of-connect-by-prior-of-oracle-in-sql-server. — Mischa Vreeburg, Nov 22 '18 at 16:26

Zeki Gumus · Answer 1 · 2018-11-22T16:02:32.557

2

You can use basicly LEFT JOIN. If you have static 4 parent it should work. If you have unknown parents you should do dynamic query.

SELECT U1.UserId
        ,U1.UserName
        ,U2.UserName AS Parent1
        ,U3.UserName AS Parent2
        ,U4.UserName AS Parent3
        ,U5.UserName AS Parent4
FROM Users U1
LEFT JOIN Users U2 ON U1.ParentId = U2.UserId
LEFT JOIN Users U3 ON U2.ParentId = U3.UserId
LEFT JOIN Users U4 ON U3.ParentId = U4.UserId
LEFT JOIN Users U5 ON U4.ParentId = U5.UserId

EDIT : Additional(to exclude parent users from the list) :

WHERE NOT EXISTS (SELECT 1 FROM Users UC WHERE U1.UserId = UC.ParentId)

edited Nov 22 '18 at 16:02

answered Nov 22 '18 at 15:23

Zeki Gumus

1,484
7
14

This works but I get some rows where the Username is the parent1. For example I am seeing "Test Team" as a username with Test Division as parent 1. Is there anyway to exclude these? – Jess8766 Nov 22 '18 at 15:35
@Jess8766 if you only want rows for users that are not parents you need to add WHERE U1.UserId NOT IN (SELECT ParentId FROM Users) – Dávid Laczkó Nov 22 '18 at 15:51
@Jess8766 I have added NOT EXISTS condition to the WHERE statement to exclude parent users. – Zeki Gumus Nov 22 '18 at 16:04

score 1 · Answer 2 · edited Nov 23 '18 at 03:07

select 
   tb1.UserId as UserId,
   tb1.UserName as UserName,
   tb2.UserName as Parent1, 
   tb3.UserName as Parent2, 
   tb4.UserName as Parent3, 
   tb5.UserName as Parent4 
from tbl t1 
left join tbl t2 on t2.UserId=t1.ParentID 
left join tbl t3 on t3.UserId=t2.ParentID 
left join tbl t4 on t4.UserId=t3.ParentID  
left join tbl t5 on t5.UserId=t4.ParentID;

you need to do 4 left joins in order to fetch 4 parent details

Prem · Answer 3 · 2018-11-23T11:18:47.063

In order to get all parent or child, it's efficient to use a recursive function which would fetch the whole hierarchy.

Sample Table:

CREATE TABLE #TEST
(
[Name] varchar(100),
ManagerName Varchar(100),
Number int
)

Insert some values

Insert into Test values
('a','b'), ('b','c'), ('c','d'), ('d','e'), ('e','f'), ('f','g')

Create recursive function as below

CREATE FUNCTION [dbo].[fnRecursive] (@EmpName Varchar(100), @incremental int)
RETURNS @ret TABLE 
(

 ManagerName varchar(100),
 Number int
)
AS
BEGIN
  Declare @MgrName varchar(100)

  SET @MgrName = (Select ManagerName from test where [name] = @EmpName)

  Insert into @ret values (@MgrName, @incremental)
  if(@MgrName is not null)
  BEGIN
     SET @incremental = @incremental + 1;
     Insert into @ret
     Select ManagerName, Number  from [fnRecursive](@MgrName, @incremental)

  END

   RETURN;
END

If this function is joined with table, it should list the hierarchy for all employees

CREATE TABLE #TEST
(
  [Name] varchar(100),
  ManagerName Varchar(100),
  Number int
)



 Insert into #TEST
 Select x.[Name], x.ManagerName,x.number from (
 select t.[Name],a.ManagerName as managerName, a.number as number  from TEST t outer apply
 (
    select * from [fnRecursive](t.[Name],1)
 ) a)
 x

Select * from #Test

If we do a pivot on the table (excluding the 'Number' column). Assuming we store in the table "#temp" it should list all the managers as a column.

 DECLARE @cols AS NVARCHAR(MAX),
 @query  AS NVARCHAR(MAX);

 SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.[ManagerName] ) 
        FROM #temp c
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')



  set @query = 'select * from #temp
        pivot 
        (
            min([managername])
            for managername in (' + @cols + ')
        ) p '

  execute(@query)

But this doesn't name the column as 'Parent1', 'Parent2' instead with the dynamic column name.

Link below should help to set custom column name for the dynamic pivot table

 https://stackoverflow.com/questions/16614994/sql-server-pivot-with-custom-column-names

Andy N · Answer 4 · 2018-11-23T10:20:45.237

Use a recursive CTE to get the levels then pivot to put them in columns:

WITH cte(UserID, Logon, ParentID, ParentLogon, ParentLevel) AS
(
  SELECT UserID, Logon, ParentID, Logon,  0
  FROM users
  UNION ALL
  SELECT u.UserID, u.Logon, u.ParentID, cte.ParentLogon, ParentLevel + 1
  FROM users u
  JOIN cte ON cte.UserID = u.ParentID
)
SELECT UserId, Logon, Parent1, Parent2, Parent3, Parent4 FROM cte
PIVOT (
  MAX(ParentLogon)
  FOR ParentLevel
  IN (
    1 AS Parent1,
    2 AS Parent2,
    3 AS Parent3,
    4 AS Parent4
  )
)

See SQL Fiddle example

SQL - get all parents/childs?

4 Answers4