Jeff Moden

Forum Replies Created

Viewing 15 posts - 48,691 through 48,705 (of 59,102 total)

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

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

August 19, 2008 at 6:19 pm

#860333
RE: Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

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

August 19, 2008 at 6:16 pm

#860332
RE: Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

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

August 19, 2008 at 6:12 pm

#860330
RE: Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

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

August 19, 2008 at 5:51 pm

#860329
RE: Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

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

August 19, 2008 at 5:41 pm

#860328
RE: Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

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

August 19, 2008 at 5:31 pm

#860327
RE: Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

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

August 19, 2008 at 5:29 pm

#860326
RE: Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

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

August 19, 2008 at 5:23 pm

#860324
RE: Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

Peter Smith (8/19/2008)
Jeff Moden (8/18/2008)
Comments posted to this topic are about the item
What a performance! Access SQL had the Pivot command from 1992, it's taken 13 years for...
--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)

August 19, 2008 at 5:09 am

#859900
RE: Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

Phil Factor (8/19/2008)
[p]I hope I'm not mentioning anything that you are covering in subsequent articles, but when you need row and column totals, averages, or other aggregations, then suddenly the...
--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)

August 19, 2008 at 5:03 am

#859895
RE: GOTO vs WHILE

DB_Andrew (8/18/2008)
There are times when you don't always have the time to come up with the proper set based solution. Now I'm not saying you should take the quick...
--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)

August 18, 2008 at 10:19 pm

#859794
RE: Debugger For SQL

Actually, I've gotta be up in 5 hours... I'll finish this tomorrow evening. Heh... don't change that channel! 😛
--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)

August 18, 2008 at 9:58 pm

#859790
RE: Debugger For SQL

Steve Jones - Editor (8/18/2008)
Or write up a short article. It's always good to see how someone tests and their results.
Heh... but since he challenged my method, I get the...
--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)

August 18, 2008 at 9:55 pm

#859788
RE: Debugger For SQL

Here's some "simpler" code for the "single run" test... the cteTally split blows the SELECT/UNION ALL method away by 51.7 times on my box. I didn't put it in...
--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)

August 18, 2008 at 9:53 pm

#859787
RE: Need Help In Getting Required Result

Trevor,
This thread kinda got hijacked... are you all set?
--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)

August 18, 2008 at 7:41 pm

#859760

Viewing 15 posts - 48,691 through 48,705 (of 59,102 total)