Why we used Aggregate function in pivot and cross tab queries ?

Question

Why we used Aggregate function in pivot and cross tab queries ?

Shadab Shah

SSCarpal Tunnel

Points: 4759
More actions
December 12, 2013 at 10:25 am

#280306

Why do we used aggregate function in pivot operator and cross tab queries . Any link to blog or video explanning my question can do.
Thanks.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

Shadab Shah (12/12/2013)
Why do we used aggregate function in pivot operator and cross tab queries . Any link to blog or video explanning my question can do.
Thanks.

No offense intended but have you tried google? Using the EXACT wording from your post...

https://www.google.com/#q=Why+do+we+used+aggregate+function+in+pivot+operator+and+cross+tab+queries

Look at the very first return.

Generally speaking pivot or crosstabs are used as summary data. The details are aggregated.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

2 great articles by Jeff Moden on the subject:

http://www.sqlservercentral.com/articles/T-SQL/63681/

http://www.sqlservercentral.com/articles/Crosstab/65048/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 3

Shadab Shah (12/12/2013)
Why do we used aggregate function in pivot operator and cross tab queries . Any link to blog or video explanning my question can do.
Thanks.

The simple answer is "because" we want data from different rows to show up on the same row. One of the better ways to do that is to use aggregation even if there's only one value and the aggregation returns that same value.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)