Hi all,

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, 'f***@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!

Ben

Hi, Ben

You can do this in a stored procedure, using dynamic sql (there are
plenty of resources available: just search for crosstab or pivot
query). However you cannot do it in a view, unless you know in advance
all the possible values for the properties, because a view has to have
a fixed set of columns.

Razvan

Ben,

This is not, - repeat, not - a normalized database.
Similarly, if you store your name as follows

CREATE TABLE Name(NameID INT, CharacterNumber, Character)
INSERT Name VALUES(1,1,'B')
INSERT Name VALUES(1,2,'e')
INSERT Name VALUES(1,3,'n')

this is also not normalization, is it?

Update Statement
Date Range
UNDO
XP_SENDMAIL Overload in SQL 2005
how to do this