Using SQL PIVOT with non-aggregate column

I was banging my head on my desk for a while over this one, hopefully this will save you the pain…

I wanted to use SQL 2005’s PIVOT function except the data I was trying to PIVOT was a text column, not an aggregate of a column.  However, the business rule for this table was a 1:1 rule so there’d never be anything to aggregate anyways (even when the data is numeric).

What got me at first was the “Incorrect syntax near the keyword 'FOR'.” error message which didn’t make a whole lot of sense until I realized that added a SUM(1) resolved the problem (hence, the requirement for an aggregate column).

So how, might you ask, do you work around this?  Well, you don’t – the PIVOT function only takes an aggregate value after all.  That being said, our friend MAX and MIN don’t require a numeric value to be passed to them – they are perfectly happy accepting a varchar or nvarchar value.

So instead of trying

PIVOT([MyTextColumn] FOR [MyHeader] IN ([List],[Of],[Columns]))

Use

PIVOT(MAX([MyTextColumn]) FOR [MyHeader] IN ([List],[Of],[Columns]))