Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Expand / Collapse
Author
Message
Posted Tuesday, December 2, 2008 10:15 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:24 PM
Points: 36,995, Visits: 31,522
Comments posted to this topic are about the item Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #612568
Posted Wednesday, December 3, 2008 2:34 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:55 AM
Points: 2,366, Visits: 1,845
Another masterpiece.

Small typo -
You have a single quote missing at the end of the date for the code under OLD fashioned swap.

"SET @EndDate = '2008-01-15 "


"Keep Trying"
Post #612631
Posted Wednesday, December 3, 2008 5:03 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:24 PM
Points: 36,995, Visits: 31,522
Chirag (12/3/2008)
Another masterpiece.

Small typo -
You have a single quote missing at the end of the date for the code under OLD fashioned swap.

"SET @EndDate = '2008-01-15 "


Thanks Chirag. I don't know how the heck I do that... I'm real careful about testing the code before I put it in the article, but I guess I haven't mastered the art of copy'n'paste, yet. As much as I reread these things before I submit them, I also miss the occasional truncation of a word or the misspelling of "an" as "and" or have an out-of-place "the" in the text. I've found a couple of such errors, unfortunately, after the article was published. Guess I can't add "proof reader" to the resume.

Thanks again for the catch.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #612710
Posted Wednesday, December 3, 2008 7:58 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:37 AM
Points: 5,353, Visits: 1,389
Jeff excellent. Before reading this I was going through the discussion of your part 1 article. As I said before I learnt lot from your discussions also. :) This is awesome series.

:)




Post #612861
Posted Wednesday, December 3, 2008 8:00 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:24 PM
Points: 36,995, Visits: 31,522
Thanks for the great feedback Anirban. Great to know that folks get things out of these articles. :)

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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #612864
Posted Wednesday, December 3, 2008 8:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:05 AM
Points: 2,897, Visits: 5,981
Another Great article Jeff. Very informative and useful.

-Luke.


To help us help you read this

For better help with performance problems please read this
Post #612870
Posted Wednesday, December 3, 2008 8:08 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:51 PM
Points: 478, Visits: 1,413
Great article Jeff!
Do you think this will perform better than a dynamic pivot?
Post #612871
Posted Wednesday, December 3, 2008 8:13 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 9, 2013 1:26 PM
Points: 60, Visits: 258
I also enjoyed the article. I like that you include applications and explanations of best some practices, e.g. the discussion on using a SELECT instead of an IF under the Variables for the Start and End Dates section. It is quite helpful that the article is linked to other articles for more basic information. I also like that you distinguish between a best practice and a my-preference.

Thanks. :)


Paul DB
Post #612876
Posted Wednesday, December 3, 2008 8:18 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:34 AM
Points: 1,566, Visits: 1,851
Awesome! Having spent years using all sorts of languages to prepare cross-tabs, I really love the results here.

I, too, went back and read the discussion on the first article. Actually, my main reason for this post is so I get emailed on this discussion! :)

Thanks, Jeff!
Post #612881
Posted Wednesday, December 3, 2008 8:25 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:24 PM
Points: 36,995, Visits: 31,522
ggraber (12/3/2008)
Great article Jeff!
Do you think this will perform better than a dynamic pivot?

Thanks for the feedback and absolutely! :)


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #612888
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse