DataCCIW

cust_CCIW_v_profile_tree

Oct 27th, 2020
730
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE [ArenaDB]
  2. GO
  3.  
  4. /****** Object:  View [dbo].[cust_CCIW_v_profile_tree]    Script Date: 10/27/2020 4:03:56 PM ******/
  5. SET ANSI_NULLS ON
  6. GO
  7.  
  8. SET QUOTED_IDENTIFIER ON
  9. GO
  10.  
  11.  
  12. CREATE VIEW [dbo].[cust_CCIW_v_profile_tree]
  13. AS WITH profiletree(Root,
  14.                     Root_ID,
  15.                     Parent,
  16.                     Name,
  17.                     Level,
  18.                     ID,
  19.                     Branch)
  20.         AS (SELECT profile_name AS Root,
  21.                    profile_id AS Root_ID,
  22.                    CAST('' AS VARCHAR(100)) AS Parent,
  23.                    profile_name AS Name,
  24.                    CAST('0' AS INT) AS Level,
  25.                    profile_id AS ID,
  26.                    CAST(profile_id AS VARCHAR) AS Branch
  27.             FROM     core_profile AS profiles
  28.             UNION ALL
  29.             SELECT tree.Root,
  30.                    tree.Root_ID,
  31.                    tree.Name AS Parent,
  32.                    profiles.profile_name AS Name,
  33.                    tree.Level + 1 AS Level,
  34.                    profiles.profile_id,
  35.                    CAST(tree.Branch + '.' + CAST(profiles.profile_id AS VARCHAR) AS VARCHAR) AS Branch
  36.             FROM   core_profile AS profiles
  37.                    INNER JOIN profiletree AS tree ON profiles.parent_profile_id = tree.id)
  38.         SELECT PT.Name,
  39.                PT.ID AS profile_id,
  40.                PT.Parent,
  41.                PT.Root,
  42.                PT.Root_ID,
  43.                PT.Branch,
  44.                active
  45.         FROM   profiletree AS PT
  46.                INNER JOIN core_profile ON PT.ID = core_profile.profile_id;
  47. GO
  48.  
  49.  
  50.  
RAW Paste Data