Pivot Tables with Dynamic Columns
Hi, 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'
Hope this helps.