Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
-
Well Mr. Moden, you've done it again. A superbly useful article.
You're ongoing contributions are much appreciated.
-
the pivot on aggregation function works on numbers, what I want is a pivot on literal/character. for example, instead of having sum(amount) to be pivoted, I'd like to see a varchar column to be concatenated.
create table d(tablename varchar(9), columnmane varchar(9))
insert into d values ('tableA','col1')
insert into d values ('tableA','col2')
insert into d values ('tableB','col1')
insert into d values ('tableB','col2')
I want to see a result like
tablename , columnlist
---------, ----------
tablea col1, col2
tableb col1, col2
I can only use cursor to program it for now, but like to see a solution with a single pivot sql statement if anyone know how to do it.
Jiulu Sun
Senior DBA; Oracle/MSSQL/Sybase; CTV television inc. -
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
-
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.
-
Jeff,
Nice work!
I think you did a great job of explaining pivots and crosstabs
Mark
-
great article, Jeff!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
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
-
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.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.How to post questions to get better answers faster
Managing Transaction Logs -
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
Jiulu Sun
Senior DBA; Oracle/MSSQL/Sybase; CTV television inc. -
Peter Smith (8/19/2008)
Jeff Moden (8/18/2008)
Comments posted to this topic are about the itemWhat a performance! Access SQL had the Pivot command from 1992, it's taken 13 years for SQL proper to catch up. About to convert an Access db to a SQL Express back-end. I imagine the X-tab queries will be a major nightmare.
pj
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.
-Scott Simmons
-
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?
Reagards
-
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?
Reagards
For this requirement, it really depends on what you are looking for. If you are looking for a set number of years (e.g. always report the last 5 years), then it can easily be done using the same techniques outlined in the article.
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.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.How to post questions to get better answers faster
Managing Transaction Logs -
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.
Nicole Bowman
Nothing is forever.
-
Ian Gibson (8/19/2008)
That's interesting. I had my doubts as to how useful pivot might be. It's good to see it backed up by examples and statistics.I'm looking forward to what you have to say about dynamic cross tabs. I recently had to do a cross tab for an electronic voting system using proportional representation where each election can have a different number of candidates. The only solution I could come up with in the time available works but is so complicated as to be ridiculous.
Thanks, Ian. When I first saw the Pivot command, I felt the same way and still do. I think they made it more complicated than it needs to be especially if you consider the simplicity of the Cross-Tab aggregate method. 🙂
--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 -
Chris Morris (8/19/2008)
Really nice to read, Jeff, and absolutely spot on.I've had to do pivots in the last couple of weeks, with an unknown number of output (transposed) columns with unknown names, as in the following. It will be interesting to see your solution, you have a knack of coming up with "why the heck didn't I think of that?" solutions!
Thanks, Chris... I really appreciate the feedback and the example. I'm gonna play with it. 🙂
Say, would you mind a huge favor please? Your good example code is a bit wide and makes it so everyone has to scroll right to read everything. Could I trouble you to "break" the line that starts with "SET @SQLstr = 'SELECT HospitalName" a couple of times just before a plus sign or two just to narrow it up? Thanks a bunch.
--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
Viewing 15 posts - 16 through 30 (of 243 total)
You must be logged in to reply to this topic. Login to reply