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

Question

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

Viewing 15 posts - 211 through 225 (of 243 total)

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

Dennissinned Ten Centuries Points: 1318 More actions · Answer 1

Wow. Really great article. I will never again use pivot. CROSS TAB all the way.

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

david.c.holley (8/6/2010)
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

Care to post the code, David?

--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 3

Tom Garth (8/6/2010)
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,

Heh... I don't know about you, Tom, but I [font="Arial Black"]AM[/font] lazy... that's why I use the cross tab method. The "CPR" (Cut, Paste, Replace) method works really well in cross tabs. 😀

Thanks for the feedback, Tom. I appreciate it especially on the subject of readability.

--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 4

Dennissinned (8/6/2010)
Wow. Really great article. I will never again use pivot. CROSS TAB all the way.

BWAA-HAA!!! I try not to use absolutes like the word "never" but I definitely agree in this case. Thanks for the feedback, Dennis.

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

SQL Server Youngling Old Hand Points: 360 More actions · Answer 5

Great article! Well worth the read, and probably future re-reads.

But what are EAV's, NVP's, and CTE's? Definitions of these, or links to other articles explaining them, would improve this article.

I know a number of TLA's, or three letter acronyms, but these are not among them.

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

SQL Server Youngling (8/6/2010)
Great article! Well worth the read, and probably future re-reads.
But what are EAV's, NVP's, and CTE's? Definitions of these, or links to other articles explaining them, would improve this article.
I know a number of TLA's, or three letter acronyms, but these are not among them.

Actually, I promised a 3rd cross tab article on EAVs and NVPs. I'll try to get it out sometime in the next month.

Thanks for stopping by and thanks for the feedback. 🙂

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

le_billy Old Hand Points: 312 More actions · Answer 7

Thank you Jeff for your articles on Pivot and cross tabs, your example have given me some new ways to strengthen my code.

Billy

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

le_billy (8/6/2010)
Thank you Jeff for your articles on Pivot and cross tabs, your example have given me some new ways to strengthen my code.
Billy

Heh... you're one of the few that realize it's not all about cross tabs and pivots. Thanks for the feedback, Billy.

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

Howard C. BAchtel-438731 SSCrazy Points: 2384 More actions · Answer 9

Jeff,

You responded earlier to my question by pointing me to these articles. They are very helpful and I really appreciate it.

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

Howard C. BAchtel-438731 (8/10/2010)
Jeff,
You responded earlier to my question by pointing me to these articles. They are very helpful and I really appreciate it.

Thanks for taking the time to write a bit of feedback, Howard. I appreciate it and I'm glad I could help. 🙂

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

le_billy Old Hand Points: 312 More actions · Answer 11

Jeff,

I was having difficult time understanding how Pivot work, so I was searching the SSC forum and found thousand and thousand of posts which make my head spin(Now learn to search articles first 😛 ).

WayneS has a link to your Cross Tabs and Pivots article on his signature line(thank you WayneS 🙂 ), so I took a peak, WOW!!! what a life changing moment. I like to thank you for taking the time to write such a clear and in simple detail article for us beginners.

And I want to thank you for point it out that I don't need pivot for this particular report that I was writing, all the long I was cross tab myself into a corner :). Now I know where to go and improve my code.

As always, you and other folks in this forum has stimulate my thinking process, challenge me to be better.

Sincerely,:hehe::hehe::hehe::hehe::hehe::cool::cool::cool::cool::w00t::w00t::w00t::w00t::w00t::w00t:

Billy Le

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

le_billy (9/1/2010)
Jeff,
I was having difficult time understanding how Pivot work, so I was searching the SSC forum and found thousand and thousand of posts which make my head spin(Now learn to search articles first 😛 ).
WayneS has a link to your Cross Tabs and Pivots article on his signature line(thank you WayneS 🙂 ), so I took a peak, WOW!!! what a life changing moment. I like to thank you for taking the time to write such a clear and in simple detail article for us beginners.
And I want to thank you for point it out that I don't need pivot for this particular report that I was writing, all the long I was cross tab myself into a corner :). Now I know where to go and improve my code.
As always, you and other folks in this forum has stimulate my thinking process, challenge me to be better.
Sincerely,:hehe::hehe::hehe::hehe::hehe::cool::cool::cool::cool::w00t::w00t::w00t::w00t::w00t::w00t:
Billy Le

I was having a really bad day on multiple fronts today, Billy, and it got to be one of those days where it was no longer a matter of how many sticks I had in the fire but how many fires I had sticks in.

Thank you for taking the time to post the wonderful feedback above. You really made my day! 🙂

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

WayneS has a link to your Cross Tabs and Pivots article on his signature line(thank you WayneS 🙂

Thank you for the reference and for helping others, as well, Wayne.

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

Paul White SSC Guru Points: 150457 More actions · Answer 14

Jeff,

Just so you know, I had a similar reaction from someone asking about how to do a dynamic PIVOT via Twitter's #sqlhelp hash tag today. He/she was very impressed.

I hope today is a better day for you 🙂

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

LutzM SSC Guru Points: 107049 More actions · Answer 15

Jeff,

if I would have received 1$ each time a link to one of your articles (just to name Tally, CrossTab and DynamicCrossTab) did help an OP to solve one of their issues, I'd probably could change my job into part time... You might notice that I didn't included the QuirkyUpdate in that list. That's not because it didn't help as much as the others did. It's just to avoid starting another fire.... (but in reality it's on that list as well 😉 )

Unfortunately, people tend to not posting a "thank you" note once they succeeded but almost always complain if it doesn't work as they expected (mostly due to misunderstanding or misapplying a concept).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]