How to transpose rows into columns?

My database allows User Defined Fields(UDF) and during select, I want to combine the regular table fields with the UDFs in another table. Here is an example.

Table Person

Id  FirstName   LastName
1    John        Smith
2    Jim         Adams

Table UDF
PersonId  FieldName FieldValue
2      Age           开发者_C百科28
2      Status    Available

Result of query
Id  FirstName  LastName Age   Status
1   John      Smith     NULL  NULL
2   Jim   Adams     28    Avalable

I am using SQL server 2008 and would like to see a sample using Pivot or UnPivot.

Thanks

;With Person As
(
SELECT 1 AS Id, 'John' AS FirstName, 'Smith' AS LastName UNION ALL
SELECT 2 AS Id, 'Jim' AS FirstName, 'Adams' AS LastName 
)
,
UDF AS
(
SELECT 2 PersonId, 'Age' FieldName, '28' FieldValue UNION ALL
SELECT 2, 'Status', 'Available'
)
SELECT Id, FirstName, LastName, Age, Status
FROM Person p
LEFT OUTER JOIN UDF ON p.Id = UDF.PersonId
PIVOT (MAX(FieldValue) FOR FieldName IN ([Age],[Status])) pvt

You could generate this string dynamically and use dynamic SQL if you do not know the columns required in advance.

Unfortunately you would either hafe to know the columns before hand to use PIVOT

Or you would have to create it dynamically

see

T-SQL Dynamic Pivot Table Examples for SQL Server 2008