-- Create the denormalized table
CREATE TABLE	#Denormalized
		(
			RowID INT IDENTITY(1, 1) PRIMARY KEY,
			Col1 DATETIME,
			Col2 DATETIME,
			Col3 DATETIME,
			Col4 INT,
			Col5 INT
		)

-- Populate denormalized table
INSERT	#Denormalized
SELECT	1, 2, 3, 4, 5

-- Show the denormalized table
SELECT	*
FROM	#Denormalized

-- Prepare datetime normalizing
CREATE TABLE	#dt
		(
			RowID INT CONSTRAINT Fk_dt REFERENCES #Denormalized(RowID),
			dt DATETIME,
			tp TINYINT,	-- Type 1,2 or 3 according to denormalized table DATETIMEs
			PRIMARY KEY
			(
				RowID,
				tp
			)
		)

-- Populate new datetime table
INSERT	#dt
SELECT	RowID,
	Col1,
	1
FROM	#Denormalized

INSERT	#dt
SELECT	RowID,
	Col2,
	2
FROM	#Denormalized

INSERT	#dt
SELECT	RowID,
	Col3,
	3
FROM	#Denormalized

-- Prepare int normalizing
CREATE TABLE	#int
		(
			RowID INT CONSTRAINT Fk_dt REFERENCES #Denormalized(RowID),
			i INT,
			tp TINYINT,	-- Type 1 or 2 according to denormalized table for INTs
			PRIMARY KEY
			(
				RowID,
				tp
			)
		)

-- Populate new datetime table
INSERT	#int
SELECT	RowID,
	Col4,
	4
FROM	#Denormalized

INSERT	#int
SELECT	RowID,
	Col5,
	5
FROM	#Denormalized

-- Now drop redundant columns
ALTER TABLE	#Denormalized
DROP COLUMN	Col1,
		Col2,
		Col3,
		Col4,
		Col5

-- Show resultset
SELECT		d.RowID,
		q.Col1,
		q.Col2,
		q.Col3,
		i.Col4,
		i.Col5
FROM		#Denormalized AS d
LEFT JOIN	(
			SELECT		RowID,
					MAX(CASE WHEN tp = 1 THEN dt ELSE NULL END) AS Col1,
					MAX(CASE WHEN tp = 2 THEN dt ELSE NULL END) AS Col2,
					MAX(CASE WHEN tp = 3 THEN dt ELSE NULL END) AS Col3
			FROM		#dt
			GROUP BY	RowID
		) AS q ON q.RowID = d.RowID
LEFT JOIN	(
			SELECT		RowID,
					MAX(CASE WHEN tp = 4 THEN i ELSE NULL END) AS Col4,
					MAX(CASE WHEN tp = 5 THEN i ELSE NULL END) AS Col5
			FROM		#int
			GROUP BY	RowID
		) AS i ON i.RowID = d.RowID

-- Clean up
DROP TABLE	#Denormalized,
		#dt,
		#int

quote:

---

Originally posted by visakh16

In future, are there chances of a new type coming which might require more than 20 columns for its properties?

---