cust_CCIW_v_profile_tree

USE [ArenaDB]
GO

/****** Object:  View [dbo].[cust_CCIW_v_profile_tree]    Script Date: 10/27/2020 4:03:56 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[cust_CCIW_v_profile_tree]
AS WITH profiletree(Root,
                    Root_ID,
                    Parent,
                    Name,
                    Level,
                    ID,
                    Branch)
        AS (SELECT profile_name AS Root,
                   profile_id AS Root_ID,
                   CAST('' AS VARCHAR(100)) AS Parent,
                   profile_name AS Name,
                   CAST('0' AS INT) AS Level,
                   profile_id AS ID,
                   CAST(profile_id AS VARCHAR) AS Branch
            FROM     core_profile AS profiles
            UNION ALL
            SELECT tree.Root,
                   tree.Root_ID,
                   tree.Name AS Parent,
                   profiles.profile_name AS Name,
                   tree.Level + 1 AS Level,
                   profiles.profile_id,
                   CAST(tree.Branch + '.' + CAST(profiles.profile_id AS VARCHAR) AS VARCHAR) AS Branch
            FROM   core_profile AS profiles
                   INNER JOIN profiletree AS tree ON profiles.parent_profile_id = tree.id)
        SELECT PT.Name,
               PT.ID AS profile_id,
               PT.Parent,
               PT.Root,
               PT.Root_ID,
               PT.Branch,
               active
        FROM   profiletree AS PT
               INNER JOIN core_profile ON PT.ID = core_profile.profile_id;
GO