Question about the merits of Pivot vs Case

Question

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 write 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?

Answer 1

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