Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

Question

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

Viewing 15 posts - 196 through 210 (of 243 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

srweal (5/6/2010)
You are a gun Jeff. Got this tasty article in my inbox and was so glad I stopped by for a read. PIVOT scared me off a few months back and I have been pondering how to get some answers out of my EAV tables.
This approach seems to get me on the way. Love it.
Btw, in the conclusion of your Part 1 you mention something in future parts about EAV/NVP. Is that going to be in a forthcoming article, or have I misinterpreted what you were getting at there?

Thanks for the great feedback. And, no, you're not misinterpreting anything. I did promise another article on EAV/NVP usage and never got to it. I should probably get crackin' on one since I made the promise, huh?

Nice to see another person who prefers cross tabs to pivots.

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Toby Harman (8/6/2010)
As an aside (and without reading all 20 pages on this thread) I played around with the PIVOT operator on a SELECT statement and found some interesting performance metrics with large-ish datasets (100,000 rows).

Thanks for stopping by and thanks for the feedback.

Shifting gears, what "interesting performance metrics with large-ish datasets" did you find?

--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)
Intro to Tally Tables and Functions

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

I soooo prefer the CASE syntax to PIVOT! 🙂

I did learn something new, which honestly doesn't happen all that often for me when it comes to the relational engine - that preaggregating the data and improve pivoting performance especially on larger datasets. Quite interesting!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Gillbert Newbie Points: 1 More actions · Answer 4

Dear All,

This all [HARD coded] like year or month whatever

in my situations, i don't want to [HARD Coded] my pivt value,

how to build the qry, if anybody's says welcome.

Otherwise i will pass it to.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

Gillbert (8/6/2010)
Dear All,
This all [HARD coded] like year or month whatever
in my situations, i don't want to [HARD Coded] my pivt value,
how to build the qry, if anybody's says welcome.
Otherwise i will pass it to.

No problem. See Part 2 of this series...

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

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

TheSQLGuru (8/6/2010)
I soooo prefer the CASE syntax to PIVOT! 🙂
I did learn something new, which honestly doesn't happen all that often for me when it comes to the relational engine - that preaggregating the data and improve pivoting performance especially on larger datasets. Quite interesting!

Thanks for the feedback, Kevin. I especially like the fact that you like the CASE syntax of a cross tab.

Again, I have to give credit to fellow speed-phreak Peter "PESO" Larrson for coining the phrase "Pre-Aggregation". It's useful in places other than cross tabs, as well, but works especially well for such things.

--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)
Intro to Tally Tables and Functions

roger_os SSC Eights! Points: 831 More actions · Answer 7

roger_os

SSC Eights!

Points: 831

August 6, 2010 at 4:47 am

#1203377

Thanks for the article Jeff.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

roger_os (8/6/2010)
Thanks for the article Jeff.

You bet. Thanks for stopping by.

--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)
Intro to Tally Tables and Functions

joel.weiss 70857 Grasshopper Points: 21 More actions · Answer 9

I want dynamic pivots, i,e., pivot a table without knowing what exists in the pivot data. Nothing short will satisfy me. It seems like a religious ban on thinking by both Microsoft and the "hard-core" developers on these forums. Just do what Access and Excel both do very well, but do it in Sql Server. Is this too much to ask?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

joel.weiss 70857 (8/6/2010)
I want dynamic pivots, i,e., pivot a table without knowing what exists in the pivot data. Nothing short will satisfy me. It seems like a religious ban on thinking by both Microsoft and the "hard-core" developers on these forums. Just do what Access and Excel both do very well, but do it in Sql Server. Is this too much to ask?

Why are you picking on 'the "hard-core" developers on these forums' and why do you believe there's a "religions ban on thinking"? Before you explain, you might try a bit of an attitude change... a lot of those supposedly limited thinkers might actually be able to help you out. 😉

You might also try doing a search to find articles about dynamic pivots...

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

--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)
Intro to Tally Tables and Functions

joel.weiss 70857 Grasshopper Points: 21 More actions · Answer 11

Jeff, you are totally right. Must have been a bad breakfast... my apologies. -Joel

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 12

joel.weiss 70857 (8/6/2010)
Jeff, you are totally right. Must have been a bad breakfast... my apologies. -Joel

Thanks for that, Joel. Heh... I've had coffee from a soapy cup before... you're not the only one. 🙂

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 13

Hey Joel,

Not sure you saw it in my previous post because of the "edit" I did... Check out the following article. While it may not be as intelligent as Access or Excel (I agree... gotta love pivots in both of those), it does bring SQL Server a bit closer... heh... even if it was written by one of those "hard-core" developers. 😛 http://qa.sqlservercentral.com/articles/Crosstab/65048/

--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)
Intro to Tally Tables and Functions

david.c.holley SSCrazy Points: 2423 More actions · Answer 14

I'd consider myself an advanced beginner. I started playing around with a table valued function mostly to see what would happen. I ended up creating one that uses a function to aggregate child records for a parent into columns by category - think initials of employees by department. I then created a view that joins projects to the resultant table giving me a list of projects with staff information as in

Project Name DeptXEmployees DeptYEmployees DeptZEmployees

ABC Liquors AH, AW, RT DH MV, EJ, BP, SS, MM, BC

Tom Garth SSCertifiable Points: 6193 More actions · Answer 15

Terrific article Jeff, and welcome too. I thought I was being lazy for sticking to the cross tab format for the last few years. I couldn't agree with you more regarding readability, and that important quality shouldn't be ignored.

Thanks,

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers