0

http://technet.microsoft.com/en-us/library/ms177410(SQL.105).aspx

In some cases I have seen the aggregate function as sum, Max or avg. How to determin which aggregate function to use in the PIVOT statement? Thank you, smith

Taryn
  • 242,637
  • 56
  • 362
  • 405
LaysomeSmith
  • 661
  • 2
  • 6
  • 14

1 Answers1

3

You can use any aggregate function in the PIVOT but it depends on what your requirement is for the query.

Aggregate Functions include:

SUM()
MIN()
MAX()
COUNT()
AVG()
Taryn
  • 242,637
  • 56
  • 362
  • 405
  • http://stackoverflow.com/questions/11617713/how-to-create-a-pivottable-in-transact-sql In the above case I had "condition_result" as int data type. It worked. After changing to bit data type it stopped working. Why? How to decide which aggregate function is right. Thank you – LaysomeSmith Aug 03 '12 at 22:02
  • @LaysomeSmith I am not sure what you are asking in referencing your other question. – Taryn Aug 03 '12 at 22:04
  • 1
    @LaysomeSmith if you are using a bit field, then you need to wrap it in a `CASE` statement to get a value for the field. See [SQL Fiddle](http://sqlfiddle.com/#!3/d3769/2) with demo – Taryn Aug 03 '12 at 22:09
  • Why? Is bit field (true or false) is not an value? I have seen examples that field returns Null also. Still I am not getting. Thank you for your reply. – LaysomeSmith Aug 03 '12 at 22:18
  • Most of the aggregate functions will not work with a `bit` datatype. What do you need to do? That will help you decide which function to use. – Taryn Aug 03 '12 at 22:21