How to transpose rows into columns?

Question

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           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

Answer 1

Reformatted text

Table Person

Id FirstName LastName 1 John Smith 2 Jim Adams

Table UDF

PersonId FieldName FieldValue 2 Age 28 2 Status Available

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

Answer 2

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

Answer 3

;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.