How to wrap rows in columns?

Question

My database allows user-defined fields (UDFs) and, during the selection, I want to combine regular table fields with 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 want to see a sample using Pivot or UnPivot.

thank

Answer 1

;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 can generate this row dynamically and use dynamic SQL if you do not know the required columns in advance.

Answer 2

Unfortunately, you or hafe would know the columns before using PIVOT

Or you will have to dynamically create it

cm.

T-SQL Dynamic Pivot Table Examples for SQL Server 2008