Load Normalized tables from Denormalized Data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Load Normalized tables from Denormalized Data

Author

Topic

LaurieCox

158 Posts

Posted - 2009-11-19 : 09:18:01

I have this horribly denormalized table:
[CODE]
CREATE TABLE #DenormalizedRole (
UserRole varchar(20),
OptionIdString varchar(100),
OptionDescString varchar(100)
)

INSERT INTO #DenormalizedRole (UserRole, OptionIdString, OptionDescString)
SELECT 'AUTHREQUEST', '&OPTION01&OPTION02&OPTION03&', '&Apple&Orange&Lemon&' UNION ALL
SELECT 'CLINICAL', '&OPTION02&OPTION03&', '&Orange&Lemon&' UNION ALL
SELECT 'ACCESS', '&OPTION02&OPTION04&', '&Orange&Banana&' UNION ALL
SELECT 'CLINICALSED', '&OPTION01&OPTION02&', '&Apple&Orange&'
[/CODE]
Notes:

The string columns contain data delimited by the ampersand(&).

The delimited data in the OptionIdString and OptionDescString are ordered so that the first element in OptionIdString is associated with the first element in OptionDescString (and the second with the second … and so on).

Hidden in the data is a many to many relationship between UserRole and OptionId and a one to one relationship between OptionId and OptionDesc.

I would like to take the data from the above table and load the following tables:
[CODE]
CREATE TABLE #UserRoleList (
UserRole varchar(20) PRIMARY KEY
)

CREATE TABLE #OptionList (
OptionId varchar(20) PRIMARY KEY,
OptionDesc varchar(20)
)

CREATE TABLE #UserRole_XREF_OptionId (
UserRole varchar(20),
OptionId varchar(20)
)
[/CODE]
The #UserRoleList table is easy:
[CODE]
INSERT INTO #UserRoleList
SELECT UserRole from #DenormalizedRole
[/CODE]
It is the other two tables that are more problematic. Which is my question: How do I do this?

Expected Results:
[CODE]
Table #UserRoleList

UserRole
========
AUTHREQUEST
ACCESS
CLINICAL
CLINICALSED

Table #OptionList

OptionId OptionDesc
======== ==========
OPTION01 Apple
OPTION02 Orange
OPTION03 Lemon
OPTION04 Banana

Table #UserRole_XREF_OptionId

UserRole OptionId
======== ========
AUTHREQUEST OPTION01
AUTHREQUEST OPTION02
AUTHREQUEST OPTION03
CLINICAL OPTION02
CLINICAL OPTION03
ACCESS OPTION02
ACCESS OPTION04
CLINICALSED OPTION01
CLINICALSED OPTION02
[/CODE]

Thanks,

Laurie

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-11-19 : 10:08:09

Try using a number/tally table:


INSERT INTO #OptionList
SELECT DISTINCT D1.OptionId, D2.OptionDesc
FROM
(
	SELECT UserRole
		,SUBSTRING(R.OptionIdString
				,N.Number + 1
				,CHARINDEX('&', R.OptionIdString, N.Number + 1) - N.Number - 1) as OptionId
		,ROW_NUMBER() OVER (PARTITION BY R.UserRole ORDER BY N.Number) AS FieldNo
	FROM #DenormalizedRole R
		JOIN master.dbo.spt_values N
			ON N.[Type] = 'P'
				AND N.Number < LEN(R.OptionIdString)
				AND SUBSTRING(R.OptionIdString, N.Number, 1) = '&'
) D1
	JOIN
	(
		SELECT UserRole
			,SUBSTRING(R.OptionDescString
					,N.Number + 1
					,CHARINDEX('&', R.OptionDescString, N.Number + 1) - N.Number - 1) as OptionDesc
			,ROW_NUMBER() OVER (PARTITION BY R.UserRole ORDER BY N.Number) AS FieldNo
		FROM #DenormalizedRole R
			JOIN master.dbo.spt_values N
				ON N.[Type] = 'P'
					AND N.Number < LEN(R.OptionDescString)
					AND SUBSTRING(R.OptionDescString, N.Number, 1) = '&'
	) D2
		ON D1.UserRole = D2.UserRole
			AND D1.FieldNo = D2.FieldNo

INSERT INTO #UserRole_XREF_OptionId
SELECT UserRole
	,SUBSTRING(R.OptionIdString
			,N.Number + 1
			,CHARINDEX('&', R.OptionIdString, N.Number + 1) - N.Number - 1) as OptionId
FROM #DenormalizedRole R
	JOIN master.dbo.spt_values N
		ON N.[Type] = 'P'
			AND N.Number < LEN(R.OptionIdString)
			AND SUBSTRING(R.OptionIdString, N.Number, 1) = '&'

LaurieCox

158 Posts

Posted - 2009-11-19 : 10:28:02

Wow ... that is prefect.

Thanks,

Laurie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources