Creating a view do display normalised data flattened

SQL Apprentice Question
I have created the following schema to illustrate my question:


CREATE TABLE [dbo].[tblCustomerProperty] (
[cpcusID] [int] NOT NULL ,
[cpproID] [int] NOT NULL ,
[cpValue] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[tblCustomers] (
[cusID] [int] IDENTITY (1, 1) NOT NULL ,
[cusName] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[tblProperties] (
[proID] [int] NOT NULL ,
[proName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO


ALTER TABLE [dbo].[tblCustomers] WITH NOCHECK ADD
CONSTRAINT [PK_tblCustomers] PRIMARY KEY CLUSTERED ([cusID]) ON
[PRIMARY]
GO


ALTER TABLE [dbo].[tblProperties] WITH NOCHECK ADD
CONSTRAINT [PK_tblProperties] PRIMARY KEY CLUSTERED ([proID]) ON
[PRIMARY]
GO


ALTER TABLE [dbo].[tblCustomerProperty] ADD
CONSTRAINT [FK_tblCustomerProperty_tblCustomers] FOREIGN KEY
([cpcusID]) REFERENCES [dbo].[tblCustomers] ([cusID]) ON DELETE CASCADE
,
CONSTRAINT [FK_tblCustomerProperty_tblProperties] FOREIGN KEY
([cpproID]) REFERENCES [dbo].[tblProperties] ([proID]) ON DELETE
CASCADE
GO


This is essentially a very normalized customer database. A number of
properties are defined in the tblProperties table. One record per
customer exists in the tblCustomer table, and one record per
customer/property combination exists in the tblCustomerProperty table.


I imagine this is fairly common in systems that need to be highly
configurable. Obviously it comes with it's performance overheads but
it's very flexible.


Assume the following data
INSERT INTO tblProperties(proID, proName) VALUES(1, 'Occupation')
INSERT INTO tblProperties(proID, proName) VALUES(2, 'Email')
INSERT INTO tblCustomers(cusName) VALUES('Fred Bloggs')
DECLARE @ID int
SELECT @ID = @@IDENTITY
INSERT INTO tblCustomerProperty(cpcusID, cpproID, cpValue) VALUES(@ID,
1, 'Computer Engineer')
INSERT INTO tblCustomerProperty(cpcusID, cpproID, cpValue) VALUES(@ID,
2, '...@bloggs.com')


My question is how can I create a view that will return a flat view of
customers? The view needs to dynamically include 'columns' specified
in the tblProperties table without having to be changed.


eg.


cusID cusName Occupation Email
1 Fred Bloggs Computer Engineer f...@bloggs.com


Many thanks!



Celko Answers

>> This is essentially a very normalized customer database. <<


Not only is it un-normalized (it never made it to normalized to be
denormalized!), it is full of ISO-11179 violations, lack of relational
keys, data and meta-data are mixed, etc.

What you have is called a EAV (entity-attribute-value) design flaw. It
is a very common design error among people who were asleep in their
database classes :) Google it; it is a disaster that will fall apart
from the lack of data integrity in about one year of production work.


Also, get rid of that silly "tbl-" prefix and camel case -- it makes
you look like an OO programmer who does not know that even MS gave up
on camel case and Hungarian notation.


Seriously, you need to start over from scratch with a real data model
instead a vague "thingies have properties" view of the world. "To be
is to be something in particular; to be nothign in particular or
everything in general is to be Nothing" -- Aristotle