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

Question

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

Viewing 15 posts - 31 through 45 (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

Peter Smith (8/19/2008)
Jeff, I've copied your code. Maybe it will reduce the bad language as the upscaling to SQL takes place. Thanks for the article.

Heh... been there, done that, don't wanna do it again! 🙂 Glad I could help a bit 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

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

Jack Corbett (8/19/2008)
Hey Jeff, good article. It's funny I have answered several forum questions recently using a Case embedded in an aggregate. They weren't crosstabs exactly, but similar. So, after having answered these questions I decided to post it on my blog, hoping someone might stumble upon it on a search. I was even considering submitting an article to SSC on it, but you beat me to the punch. At least the info is out there and with much more performance testing done on it than I would have done. Looking forward to the rest of the series.

Awesome, Jack... Thanks for the feedback! By the way, what's the link for your blog? I've been a fan of your post answers and would love to see your blog! 🙂

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

sunjiulu (8/19/2008)
hi Richard,
that xml code works like a charm, thanks for sharing.
jiulu
Richard Fryar (8/19/2008)
In response to sunjiulu's query, you don't need a cross-tab.
A bit of magic with XML is all that's required:
SELECT d1.tablename,
(SELECT STUFF(sep, LEN(sep), 1, '')
FROM (
SELECT columnname + ',' AS [data()]
FROM d as d2
WHERE d1.tablename = d2.tablename
FOR XML PATH('')
) AS z (sep)) AS columnlist
FROM d as d1
GROUP BY d1.tablename

Nicely done, Richard and thanks for the "cover".

Just to continue the thought, there are other ways to pull off such concatenations and some have a pitfall or two... take a look at the following link if you get the chance...

http://qa.sqlservercentral.com/articles/Test+Data/61572/

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

Jeffrey Williams (8/19/2008)
Hey Jeff, good article - reminds me of a fairly recent discussion we had on this forum 😀
Nice to see that you put what we learned in that discussion into a very useful article that can easily be referenced.

Yeap... 'bout six weeks ago if I recall correctly... the only thing missing for me was doing those bloody dual Pivots...

Do you happen to have the URL for that thread? I'd lost track of it and would like to have it posted here because a lot of good folks, like yourself, had some pretty good ideas there.

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

Jack Corbett SSC Guru Points: 184388 More actions · Answer 5

Thanks Jeff. My blog is www.wiseman-wiseguy.blogspot.com. It's also at the bottom of all my posts on the little button that says "blog". 😛

That's where I put the stuff that probably doesn't merit an SSC article or needs to be expanded on to be an article.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

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

s.c.simmons (8/19/2008)
I was pleased as punch to see that in SQL Server 2005 when my company upgraded. Then I wrote my first queries using it, and saw the performance hit. :ermm: Went back to crosstabs pretty quick. Glad to see confirmation that I wasn't doing it wrong.

Heh... I shared those same initial feelings... and now that I know the ol' cross tab method is both faster and easier to write, ya gotta wonder why they spent the time to include it in 2k5. 🙂

Thanks for the feedback, Scott.

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

ruben ruvalcaba (8/19/2008)
Great Article! pivots are great because it exposes the same information in many ways very usefull ....
In your sample you build a pivot when you already now how many columns you have...but the problem I faced is when the number of pivot columns is not a constant. Let's say you have years instead of quarters, but the number of years stored is changing year by year. how can you build a pivot like that?

That'll be part of the next article... and I absolutely guarantee I won't be using PIVOT for it... it'll be a good ol' fashioned Cross-Tab.

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

Jeffrey Williams (8/19/2008)
However, if you want dynamic number of years then you are going to need to use dynamic SQL to generate the query. I believe Jeff is going to be presenting an article on that subject as part 2.

Absolutely correct... 🙂

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

Nicole Bowman (8/19/2008)
Thanks for a very clear article. I have been wondering what the differences might be between cross tab and pivot but haven't found the time to investigate. Now I don't need to! I also think that the CASE syntax is much clearer and easier to read than the PIVOT syntax. I'll be sticking with cross tab now I know the performance is better.
I too miss the easy cross tabs in MS Access. Dynamic cross tabs in SQL are heavy going at times.

Thanks for the great feedback, Nicole... hopefully, Part of of this mini-series will make Dynamic Cross Tabs a bit easier for everyone. 🙂

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

Jack Corbett (8/19/2008)
Thanks Jeff. My blog is www.wiseman-wiseguy.blogspot.com. It's also at the bottom of all my posts on the little button that says "blog". 😛
That's where I put the stuff that probably doesn't merit an SSC article or needs to be expanded on to be an article.

Took a look at it... pretty cool, Jack. Pretty nice web site, too. I gotta learn how to do that one of these days.:)

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

JJ B (8/19/2008)
Once again, such good explanations/teaching.
I'm like an earlier poster. I haven't been able to find an advantage to the pivot syntax. (Though unpivot came in handy once.) I keep wondering and wondering why MS thought the pivot syntax would be a benefit. What do they see as the advantage over the simple CASE statement?
You not only pointed out the complexity of the pivot syntax, but gave us comparative performance stats too. Very helpful.
I'm like others on this posting in that I'm looking forward to seeing solutions on dynamic cross tabs/pivots.
Thanks. - JJ

Thanks for the feedback and the encouragement, JJ... when I write these, I ask myself how I'd like to see it written if I knew nothing about it and needed some help. I guess I owe my time in the Navy to that thought process. 🙂

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

Slope (8/19/2008)
Nicely written article, Jeff! I'm new to this forum so don't know if you've covered it already, but it might be nice to see a counterpart to this article, Converting Columns to Rows. Maybe it doesn't warrant an entire article as I realize it is pretty straight forward, but it is common to recieve data in crosstabbed format also. The reason I mention it is that before I realized how easy it is to "uncrosstab" data using SQL, I used to have complex VB code in the UI to do it, which is VERY slow. I often find that if I take off the "programmer guy" hat and put on the "database guy" hat, the database can do things like that much more efficiently.

That's great feedback, Slope! I've kinda dedicated my professional efforts to T-SQL and only look back on my ol' VB days, but I remember... heh, because of my dedication to T-SQL, some folks ask me why I don't "Think outside the box" more... my typical response is "What's wrong with the box you're in?" 😛

Like I said, great feedback and thanks for the compliment. 🙂

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

There's a bunch of good folks that took the time to "drop in" with some GREAT encouragement and tips on what I'm doing right that I haven't responded to personally... I value your very thoughtful posts as much as the others! Thanks folks!

Chirag

Paul DB

gerhard

Charles Kincaid

alennon

Mark Horninger

RBARryyoung 😉

... and, if I missed anyone, so far, please don't take it personally... I just missed you. Thank you for your participation and your comments! 🙂

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

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 14

Jeff Moden (8/19/2008)Yeap... 'bout six weeks ago if I recall correctly... the only thing missing for me was doing those bloody dual Pivots...
Do you happen to have the URL for that thread? I'd lost track of it and would like to have it posted here because a lot of good folks, like yourself, had some pretty good ideas there.

Wasn't too hard to find - but then again, I don't have over 10,000 posts to search through :w00t:

Here it is: http://qa.sqlservercentral.com/Forums/Topic521489-338-1.aspx

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Jack Corbett SSC Guru Points: 184388 More actions · Answer 15

Jeff Moden (8/19/2008)
Took a look at it... pretty cool, Jack. Pretty nice web site, too. I gotta learn how to do that one of these days.:)

Wish I could take credit for the web site. It's just a template within the NTM main web site. Plus my wife has done most of the work that has been done on it.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question