declare @Groups varchar(max)
select @Groups = (
select '|' + G.GroupName
  FROM Resource R   
    INNER JOIN ResourceSecurityGroup RG           ON R.ResourceID = RG.ResourceID   
    INNER JOIN SecurityGroup G           ON G.GroupID = RG.GroupID        
WHERE R.ResourceID = @ResourceID
 FOR XML PATH(''))

set @Groups = substring(@Groups, 2, 1024)
return @Groups

--===== Do this in a safe place for testing
    USE TempDB

--=======================================================================================
--      Make sure that none of the tables used in the demo below already exist.
--      This just ensures that everyone is on the same page for the demo.
--      Note that ALL these demo tables are in TempDB... a safe place for this demo.
--=======================================================================================
     IF OBJECT_ID('TempDB.dbo.Tally','U') IS NOT NULL 
        DROP TABLE TempDB.dbo.Tally

     IF OBJECT_ID('TempDB.dbo.OriginalTable','U') IS NOT NULL 
        DROP TABLE TempDB.dbo.OriginalTable

     IF OBJECT_ID('TempDB.dbo.ResourceSecurityGroup','U') IS NOT NULL 
        DROP TABLE TempDB.dbo.ResourceSecurityGroup

     IF OBJECT_ID('TempDB.dbo.Resource','U') IS NOT NULL 
        DROP TABLE TempDB.dbo.Resource

     IF OBJECT_ID('TempDB.dbo.SecurityGroup','U') IS NOT NULL 
        DROP TABLE TempDB.dbo.SecurityGroup

     IF OBJECT_ID('TempDB..#Work','U') IS NOT NULL 
        DROP TABLE #Work

     IF OBJECT_ID('TempDB.dbo.ApplicationResources','V') IS NOT NULL 
        DROP VIEW dbo.ApplicationResources

--=======================================================================================
--      Create and populate a Tally table.  This would normally be a permanent table and
--      NOT need to be regenerated everytime.
--=======================================================================================
--===== Create and populate the Tally table on the fly
 SELECT TOP 11000 --equates to more than 30 years of dates
        IDENTITY(INT,1,1) AS N
   INTO dbo.Tally
   FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance
  ALTER TABLE dbo.Tally
    ADD CONSTRAINT PK_Tally_N 
        PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Allow the general public to use it
  GRANT SELECT ON dbo.Tally TO PUBLIC

--=======================================================================================
--      Setup the original denormalized table so we can demonstrate how to normalize it
--      using set based methods instead of Cursors and other RBAR.
--=======================================================================================
--===== Create a fasimile of the original table
 CREATE TABLE OriginalTable
(
        ResourceName VARCHAR(50),
        IsEnabled    CHAR(1),
        Groups       VARCHAR(1024),
        CONSTRAINT PK_OriginalTable PRIMARY KEY CLUSTERED (ResourceName) 
)

--===== Populate it with data from the article
 INSERT INTO OriginalTable
        (ResourceName, IsEnabled, Groups)
 SELECT 'Resource1','Y','Grp1|Grp2|Grp3|Grp4' UNION ALL
 SELECT 'Resource2','Y','Grp1|Grp3|Grp4'      UNION ALL
 SELECT 'Resource3','Y','Grp1|Grp2|Grp4'      UNION ALL
 SELECT 'Resource4','Y','Grp1|Grp2|Grp3'      UNION ALL
 SELECT 'Resource5','Y','Grp2|Grp3'           UNION ALL
 SELECT 'Resource6','Y','Grp1|Grp4'

--=======================================================================================
--      Create the new tables as was done in the article including FK constraints
--=======================================================================================
--===== New Resource table to contain only Resource information
 CREATE TABLE dbo.Resource
(  
        ResourceID   INT         IDENTITY(1,1) NOT NULL,
        ResourceName VARCHAR(50) NOT NULL,
        IsEnabled    CHAR(1)     NOT NULL CONSTRAINT DF_Resource_IsEnabled  DEFAULT ('Y'),
        CONSTRAINT PK_Resource   PRIMARY KEY CLUSTERED (ResourceID ASC)
)

--===== New SecurityGroup table to contain only Group information
 CREATE TABLE dbo.SecurityGroup
(  
        SecurityGroupID   INT          IDENTITY(1,1) NOT NULL,
        GroupName         VARCHAR(255) NOT NULL,
        CONSTRAINT PK_SecurityGroup    PRIMARY KEY CLUSTERED (SecurityGroupID ASC)
)

--===== New ResourceSecurityGroup table provides the link between Resources and Groups
 CREATE TABLE dbo.ResourceSecurityGroup
(  
        ResourceID      INT NOT NULL,
        SecurityGroupID INT NOT NULL,
        CONSTRAINT PK_ResourceSecurityGroup PRIMARY KEY CLUSTERED 
        (ResourceID ASC, SecurityGroupID ASC)
)

--===== Create the required FK's
  ALTER TABLE dbo.ResourceSecurityGroup WITH CHECK 
    ADD CONSTRAINT  FK_ResourceSecurityGroup_Resource_ResourceID 
        FOREIGN KEY (ResourceID)
        REFERENCES  dbo.Resource (ResourceID)

  ALTER TABLE dbo.ResourceSecurityGroup WITH CHECK 
    ADD CONSTRAINT  FK_ResourceSecurityGroup_SecurityGroup_SecurityGroupID 
        FOREIGN KEY (SecurityGroupID)
        REFERENCES  dbo.SecurityGroup (SecurityGroupID)

--=======================================================================================
--      Populate the new tables without the use of RBAR
--=======================================================================================
--===== Split the groups and assign ResourceID's and SecurityGroupID's for all rows.
     -- Data is stored in a temp table and all relationships are established by this
     -- single non-RBAR step.  Even the cross-table links are established for population
     -- of the ResourceSecurityGroup table without any RBAR.
;WITH
cteSplitData AS
(
 SELECT ResourceName,
        IsEnabled,
        SUBSTRING('|'+orig.Groups, t.N+1, CHARINDEX('|', orig.Groups+'|', t.N)-t.N) AS GroupName
   FROM dbo.Tally t
  RIGHT OUTER JOIN --Necessary in case orig.Groups is NULL
        dbo.OriginalTable orig 
     ON SUBSTRING('|'+orig.Groups, t.N, 1) = '|' 
    AND t.N < LEN('|'+orig.Groups)
)
 SELECT DENSE_RANK() OVER (ORDER BY ResourceName) AS ResourceID,
        ResourceName,
        IsEnabled,
        DENSE_RANK() OVER (ORDER BY GroupName) AS SecurityGroupID,
        GroupName 
   INTO #Work
   FROM cteSplitData
  
--===== Populate the Resource table with normalized data
    SET IDENTITY_INSERT dbo.Resource ON
 INSERT INTO dbo.Resource
        (ResourceID, ResourceName, IsEnabled)
 SELECT DISTINCT
        ResourceID, ResourceName, IsEnabled
   FROM #Work
  ORDER BY ResourceID
    SET IDENTITY_INSERT dbo.Resource OFF

--===== Populate the SecurityGroup table with normalized data
    SET IDENTITY_INSERT dbo.SecurityGroup ON
 INSERT INTO dbo.SecurityGroup
        (SecurityGroupID, GroupName)
 SELECT DISTINCT
        SecurityGroupID, GroupName
   FROM #Work
  ORDER BY SecurityGroupID
    SET IDENTITY_INSERT dbo.SecurityGroup OFF

--===== Populate the ResourceSecurityGroup link table with normalized data
 INSERT INTO dbo.ResourceSecurityGroup
        (ResourceID, SecurityGroupID)
 SELECT DISTINCT
        ResourceID, SecurityGroupID
   FROM #Work
  ORDER BY ResourceID, SecurityGroupID

--=======================================================================================
--      As a sanity check, display the contents of the work table and the 3 new tables
--=======================================================================================
 SELECT * FROM #Work ORDER BY ResourceID, SecurityGroupID
 SELECT * FROM dbo.Resource ORDER BY ResourceID
 SELECT * FROM dbo.SecurityGroup ORDER BY SecurityGroupID
 SELECT * FROM dbo.ResourceSecurityGroup ORDER BY ResourceID, SecurityGroupID
GO
--=======================================================================================
--      Create the view that puts this all back together for the existing app.
--=======================================================================================
 CREATE VIEW dbo.ApplicationResources
     AS
 SELECT r.ResourceName,
        r.IsEnabled,
        STUFF((SELECT '|'+ sg.GroupName 
                 FROM dbo.ResourceSecurityGroup rsg
                INNER JOIN SecurityGroup sg 
                   ON rsg.SecurityGroupID = sg.SecurityGroupID
                  AND rsg.ResourceID = r.ResourceID
               ORDER BY sg.GroupName
                 FOR XML PATH('')),1,1,'') AS 'Groups'
   FROM dbo.Resource r
GO
--===== Sanity check the view
 SELECT * FROM dbo.ApplicationResources

ALTER View [dbo].[ApplicationResources]
AS
SELECT R.[ResourceName]
      ,R.[IsEnabled]
      ,substring((SELECT '|'+sg.GroupName  
        FROM ResourceSecurityGroup rsg
        JOIN SecurityGroup sg ON rsg.GroupID=sg.GroupID
        WHERE rsg.ResourceID=R.ResourceID
        ORDER BY GroupName
        FOR XML PATH('')),2,1000) as 'Groups'
FROM dbo.Resource R
go