Heh... Understood and appreciated... I've been there with non-compliant vendors myself. Same goes for "out sourcing"... lot's of them don't care about performance or RBAR because they can wash their hands of it once they have your money. I keep trying to invite them to one of my famous porkchop dinners, but I guess word has gotten out on that. 😛

In SQL Server 2005, there are some tools to do this quite easily without any Cursors or While loops as you'll see in the code below. The view still has a bit of RBAR but it's not too bad... it doesn't use any REPLACE's... just one STUFF.

The entire "package" below is self standing including the creation of the original data. The whole thing runs in TempDB as a nice safe place to "play". Take a look...

--===== 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

Don't forget... if you want to copy the code as formatted above, click and hold on the line just above the code box and drag to the line just below the code box. Then, copy and paste and the code should come in nicely formatted.