Pivot Tables with Dynamic Columns

Recently I ran into a situation where I had to pivot/rotate a table (change rows to columns). This makes it very easy to apply filtering and sorting on data which might have seen impossible to do before. The site I used to help me is found at:

http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

I found this a great resource and encourage anyone interested in this field to give it a read. One particular case i found for doing this is when dealing with dynamic forms. Imagine i have the following tables:

Attributes:
- AttributeID
- AttributeName

Documents:
- DocumentID
- Title

DocumentValues
- AttributeID
- DocumentID
- Value

The attributes are basically additional columns to the documents table that i can generate dynamically. The values for a document’s attributes are then stored in the DocumentValues table.

I can then construct the following sql query (sql server 2005) which will inter-change the rows to columns and give me the benefits mentioned above:

DECLARE @Cols VARCHAR(2000)SELECT @Cols = COALESCE(@Cols + ',[' + AttributeName + ']‘, ‘[' + AttributeName + ']‘)
FROM Attributes
ORDER BY AttributeName

SET @Query = N’SELECT Documents.*, ‘ + @Cols + ‘ FROM
(SELECT DocumentValues.DocumentID, DocumentValues.Value, Attributes.AttributeName FROM Attributes INNER JOIN DocumentValues ON Attributes.AttributeID = DocumentValues.AttributeID) p
PIVOT (
MAX([Value])
FOR AttributeName IN (’ + @Cols + ‘)
) AS DocumentValues INNER JOIN
Documents ON DocumentValues.DocumentID = Documents.DocumentID’