Qfyilyi

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

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)

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

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

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