Question about the merits of Pivot vs Case

Question

I have a query that returns the attributes of an object. I want the attributes to rotate in a table. I have seen pivot tables used to do something similar, but only where aggregate functions are performed on the columns in the code. I also saw cases when they did the same.

Since you have to manually write out each column in the summary, the amount of work for each of them is relatively the same. What are the advantages and limitations of one over the other?

Answer 1

I agree with Ken. I never remember the PIVOT syntax without a reference to BOL and, moreover, it is less flexible than expressing an old school case. You can have only one cumulative value that it is impossible 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 

Answer 2

I tried PIVOT and did not see the benefits, you still need to specify the name of each column (twice if I remember), and the syntax is much less intuitive than the case arguments.

Having tried several times, I returned to CASE.