Crosstab question for a "properties" table

Most of the examples for crosstabs use pivot. It looks like aggregations are what they are used for. What about if I have a dynamic properties table. So there is a table Person and a table Property and a table PropertyPersonValue. Is there any way to create a crosstab table of Person with Properties listed in the column heading and the Values in the cells?

Sure, can you post some DDL and sample data?

AMB

|||

CREATE TABLE Person

(

PersonID INT IDENTITY(1,1) NOT NULL,

[Name] VARCHAR(200),

CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED (PersonID)

)

GO

CREATE TABLE PersonProperty

(

PersonPropertyID INT IDENTITY(1,1) NOT NULL,

[Name] VARCHAR(200),

CONSTRAINT [PK_PersonProperty] PRIMARY KEY CLUSTERED (PersonPropertyID)

)

GO

CREATE TABLE PersonPropertyValue

(

PersonID INT NOT NULL,

PersonPropertyID INT NOT NULL,

[Value] VARCHAR(1000),

CONSTRAINT [PK_PersonPropertyValue] PRIMARY KEY CLUSTERED (PersonID,PersonPropertyID),

CONSTRAINT [FK_PersonPropertyValue_PersonID] FOREIGN KEY (PersonID)

REFERENCES [Person],

CONSTRAINT [FK_PersonPropertyValue_PersonPropertyID] FOREIGN KEY (PersonPropertyID)

REFERENCES [PersonProperty],

)

GO

INSERT INTO PersonProperty ([Name]) VALUES ('Eye Color')

INSERT INTO PersonProperty ([Name]) VALUES ('Title')

INSERT INTO Person ([Name]) VALUES ('Joe Smith')

INSERT INTO Person ([Name]) VALUES ('Fred Thompson')

INSERT INTO Person ([Name]) VALUES ('Sally Hamilton')

INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])

SELECT p.PersonID,pp.PersonPropertyID,'Brown'

FROM Person p,PersonProperty pp

WHERE p.Name='Joe Smith'

AND pp.Name='Eye Color'

INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])

SELECT p.PersonID,pp.PersonPropertyID,'Blue'

FROM Person p,PersonProperty pp

WHERE p.Name='Fred Thompson'

AND pp.Name='Eye Color'

INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])

SELECT p.PersonID,pp.PersonPropertyID,'Green'

FROM Person p,PersonProperty pp

WHERE p.Name='Sally Hamilton'

AND pp.Name='Eye Color'

INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])

SELECT p.PersonID,pp.PersonPropertyID,'CFO'

FROM Person p,PersonProperty pp

WHERE p.Name='Joe Smith'

AND pp.Name='Title'

INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])

SELECT p.PersonID,pp.PersonPropertyID,'Vice Presient'

FROM Person p,PersonProperty pp

WHERE p.Name='Fred Thompson'

AND pp.Name='Title'

INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])

SELECT p.PersonID,pp.PersonPropertyID,'Sr. Partner'

FROM Person p,PersonProperty pp

WHERE p.Name='Sally Hamilton'

AND pp.Name='Title'

SELECT p.Name,pp.Name AS PropertyName, ppv.Value

FROM Person p

LEFT JOIN PersonPropertyValue ppv

ON p.PersonID=ppv.PersonID

LEFT JOIN PersonProperty pp

ON pp.PersonPropertyID=ppv.PersonPropertyID

|||

Try:

declare @.columns nvarchar(max)

declare @.sql nvarchar(max)

set @.columns = stuff(

(

select ',' + cast(quotename([Name]) as nvarchar(max))

from dbo.PersonProperty

order by [Name]

for xml path('')

), 1, 1, '')

set @.sql = N'

select

*

from

(

SELECT

p.Name,pp.Name AS PropertyName, ppv.Value

FROM

dbo.Person p

LEFT JOIN

dbo.PersonPropertyValue ppv

ON p.PersonID=ppv.PersonID

LEFT JOIN

dbo.PersonProperty pp

ON pp.PersonPropertyID=ppv.PersonPropertyID

) as src

pivot

(

min(Value)

for PropertyName in (' + @.columns + N')

) as pvt

order by

[Name]

'

exec sp_executesql @.sql

go

Result:

Name Eye Color Title Fred Thompson Blue Vice Presient Joe Smith Brown CFO Sally Hamilton Green Sr. Partner

Be careful with sql injection.

PIVOT on Steroids

http://www.sqlmag.com/Articles/ArticleID/94268/pg/3/3.html

AMB

|||Thanks. So I guess pivot is not really used for this sort of thing.

|||

Nope, you are wrong.

He used the PIVOT operator, but he listed or collected all the property values on one variable and given those stored values to your PIVOT operator query...

Print @.SQL

You will get the query which is executed here..