Get data for each identifier in parent / child database in tSQL

Question

I am working with the following table

-- USER TABLE --
User_ID | Manager_ID | Code
1       | null       | ABC
2       | 1          | DEF
3       | 2          | HIJ
4       | null       | ABC

I need to know the codes related to each user, and if the code is “owned” or not. The associated code "belongs" to the user when the code is on the same line

User_ID = 1 : Native code = ABC

But I also need to know the codes associated for each user in the Manager_ID → User_ID hierarchy. This hierarchy has no maximum depth.

User_ID = 1 : Associated code = DEF (by User_ID 2) and HIJ (by User_ID 3)

In my example, I would like to get the following result

User_ID | Code | IsOwner
1       | ABC  | 1
1       | DEF  | 0
1       | HIJ  | 0
2       | DEF  | 1
2       | HIJ  | 0
3       | HIJ  | 1
4       | ABC  | 1

For the existing code, I simply executed the following query:

SELECT User_ID, Code, 1 as IsOwner
FROM User

. , "" "", , .

Common Table Expression , , - ...

USE MyBD
GO
WITH MyCTE (Manager_ID, User_ID, Code, IsOwned)
AS
(
    SELECT Manager_ID, User_ID, Code, 1 as IsOwned
    FROM User
    WHERE Manager_ID IS NULL
    UNION ALL
    SELECT u.Manager_ID, u.User_ID, u.Code, 1 as IsOwned
    FROM User AS u
    INNER JOIN Managers AS d
        ON u.Manager_ID = d.User_ID
)
SELECT Manager_ID, User_ID, Code, IsOwned
FROM Managers
GO

?

Answer 1

, , , - , .

WITH Managers 
AS
(
    SELECT Manager_ID, User_ID,   Code,  cast(User_ID as varchar(max))  as hierarchy
    FROM Users
    WHERE Manager_ID IS NULL
    UNION ALL
    SELECT u.Manager_ID, u.User_ID,  u.Code,  d.hierarchy + case when d.hierarchy <> '' then '_' else '' end +  cast(u.user_id as varchar)
    FROM Users AS u
    INNER JOIN Managers AS d
        ON u.Manager_ID = d.User_ID
)

SELECT splitdata, Code, case when splitdata = user_id then 1 else 0 end as IsOwner

FROM
 (
 SELECT *,
 -- this part, for readability, should be replace by a split function
 cast('<X>'+replace(F.hierarchy,'_','</X><X>')+'</X>' as XML) as xmlfilter from Managers F
 )a
 CROSS APPLY
 ( 
 SELECT cast(fdata.D.value('.','varchar(50)') as int) as splitdata 
 FROM a.xmlfilter.nodes('X') as fdata(D)) s
 order by splitdata, Code

. sqlfiddle.

sqlfiddle CTE, .

( , ), !

WITH Managers 
AS
(
    SELECT Manager_ID, User_ID, user_ID as topManager, Code
    FROM Users
    WHERE Manager_ID IS NULL
    UNION ALL
    SELECT u.Manager_ID, u.User_ID, d.topManager as topManager,  u.Code
    FROM Users AS u
    INNER JOIN Managers AS d
        ON u.Manager_ID = d.User_ID
)

select topManager, Code, 0 from Managers
where manager_ID is not null
union
select user_ID, Code, CASE WHEN topManager = User_Id THEN 1 ELSE 0 END from Managers
union
select Manager_ID, Code, 0 from Managers
where Manager_ID is not null;

Answer 2

. : , , , .

;WITH MyCTE (Manager_ID, User_ID, Code, IsOwned)
AS
(
    SELECT User_ID, User_ID, Code, 1 as IsOwned
    FROM [User]

    UNION ALL
    SELECT d.Manager_ID, u.User_ID, u.Code, 0 as IsOwned
    FROM [User] AS u
    INNER JOIN MyCTE AS d
        ON u.Manager_ID = d.User_ID
)
SELECT Manager_ID, User_ID, Code, IsOwned
FROM MyCTE ORDER BY Manager_ID,Code;