Question about the merits of Pivot vs Case

I have a query that returns attributes of an object. What I want is to have attributes pivoted into the table. I have seen pivot tables used to do something like this but only where aggregate functions are performed on the cols in the pivot. I have also seen case statements used to do the same thing.

Since you have to manually writ开发者_开发技巧e out each column in a pivot it's the amount of work for each is relatively the same. What are the advantage and limitations of one over the other?

I agree with Ken. I never remember the PIVOT syntax without referrring to BOL and additionally it is less flexible than the old school case statement. You can only have one aggregate meaning it is not possible to do something like.

SELECT COUNT(CASE WHEN foo='bar' THEN foo END) AS bar_count,
         SUM(CASE WHEN foo='bar' THEN foo END)  AS bar_sum
FROM your_table     

I've tried the PIVOT and saw no advantage, you still have to specify the name of every column (twice if I recall), and the syntax is far less intuitive than case statements.

After trying it a few times I went back to CASE.