Always remember that the column names returned in a result set do not have to be the same as what you eventually output at your presentation layer. For example, suppose you have stored procedure that accepts a @CurrentYear parameter and returns a sales variance between the current year and the previous year for each customer.
Read more →
Everyone seems to want to "pivot" or "cross tab" data, but knowing how to do the opposite is equally important. In fact, I would argue that the skill of "unpivoting" data is more useful and more important and more relevant to a SQL programmer, since pivoting results in denormalized data, while unpivoting can transform non-normalized data into a normalized result set.
Read more →
As David Letterman would say, wake the kids, call the neighbors, it's time for The Mailbag! Just some quickies today. Christopher writes: Greetings Jeff, First and foremost, great job with all of the blogs.
Read more →
Introduction A question I see very often in the SQLTeam forums is how to return data in a summarized form by concatenating multiple values into single CSV string columns. For example, taking data like this: FarmerName FruitName ——————– ———- Farmer Ted Apple Farmer Ted Orange Farmer Fred Orange Farmer Fred Grapes Farmer Fred Grapefruit Farmer Jed Orange … and returning the results from the database in this format: Farmer FruitList——— ——-Farmer Ted Apple,OrangesFarmer Fred Grapefruit,Grapes,OrangeFarmer Jed Orange Notice that we are only returning one row per Farmer, and the "FruitList" column returned is a concatenated comma-separated list of values in the Fruit column.
Read more →
Introduction Previously, I wrote about grouping transactions by month. Another common area of difficulty or confusion for SQL beginners is how to efficiently retrieve data just for a single month.
Read more →
Thinking "Set-Based" So, I hear you're a "set-based SQL master"! As Yoda once said, you've "unlearned what you have learned". You've trained yourself to attack your database code not from a procedural, step-by-step angle, but rather from the set-based "do it all at once" approach.
Read more →
As many of you know, I strongly recommend that you avoid using RIGHT OUTER JOINs, since they make your SQL code less readable and are easily rewritten as LEFT OUTER JOINs.
Read more →
Learn the Database Schema Before you can write any reports off of any system, you must be familiar with the schema you are working with. Print out the data dictionary, schema diagrams, documentation – anything you can find that will help you work with the database.
Read more →
Sometimes, when writing SELECTs, we come across situations in which we we need to write a join containing a condition of some sort, so that the join processed will vary for each row depending on the data.
Read more →
I've been playing around with a handy tool for creating Word and Excel files called OfficeWriter that's pretty impressive. Basically, you use Excel or Word to create templates utilizing data markers and merge fields which allow you to databind sections of the document to a data source.
Read more →
As promised in my last post, here is some performance testing to help you determine the performance benefits (if any) of performing your crosstabs at the presentation or code layer, as opposed to forcing SQL Server to do this.
Read more →