Jeff's SQL Server Blog

Need an Answer? Actually, No ... You Need a Question

Welcome!

The reason you were directed here is because you need assistance, and I am here to help.  I am not, however, here to provide you with any answers!  You see, it looks like the assistance you need is not finding an answer; it is rather that you need assistance finding a question.

As you know, there are all kinds of questions.  Questions that test memory recall.  Questions that test logic skills.  Brain-teasers and mathematical questions and so on.  But there is one requirement that all good questions must have in common before they can be answered:


In other words, if you omit important information from a question, it doesn't matter how simple or easy that question is:  It suddenly becomes very difficult, or even impossible, to answer. 

For example, consider the following question:


Seems pretty easy, right? No logic, no memorization, no trivia, no knowledge of any specific topic is required. 

So ... what's the answer?  Take a few minutes, think about it, write it down on a piece of scrap paper.  I can wait, take your time ...

What's that, you say? You can't answer that simple question!?  Why not?  I stated it very clearly, it requires a simple YES or NO response, there's nothing tricky there.  So, why would anyone have any trouble giving an answer to something so basic and simple?

The reason, of course, is because you can't see me.  You have no way of knowing what I am wearing because I did not provide you enough information!  As simple as it is, it cannot be answered; therefore, it is not a proper question!

Suppose, instead, I provided a picture of myself and asked "In this picture, am I wearing a hat?"  And, in the picture, my head is clearly visible and the fact that I am wearing a Boston Red Sox cap is very clear.  Would you be able to answer the question in that scenario?  Of course!  Suddenly, what was an impossible question to answer became very simple! 

How did that happen?

It happened, of course, because I provided you enough information to answer the question!  And that is the often problem with many of the questions we see day to day in forums asking for help.  You cannot expect an answer unless you provide a proper question with all the necessary information.  The majority of the effort by those helping others in these forums is not spent answering questions, it is spent trying to figure out what the heck the question actually is!  And that is the problem; people don't seem to realize that they can't just randomly cut and paste code or ask vague questions without any context and expect to receive help! 

Yet, requesting more information and details doesn't always go over so well ... Those looking for help seem to often have trouble understanding why the helpers need more info, why are they are so "anal" and "demanding" about minor things like database schemas, or sample data, or code samples.  "How is that important?" they wonder. "Just answer the question and provide me with some help, please!  An expert would know the answer!"

My goal today is to hopefully help you to understand why information and context is so important for even simple, basic, questions, and how providing that information suddenly transforms a poor question into a very good one that can be quickly and accurately answered. 

Let's try another example:


So, what do you think?  Is that a good question that provides all of the information?  It seems simple, right? It is just asking for basic directions. Yet, where is "work"?  Is it asking for driving directions, or walking directions, or maybe which subway lines to take?  Who knows!!?

As stated, this question simply cannot be answered!   Yet, if I just  thought about it a little and made sure to provide all of the necessary information, this "impossible" question with no answer suddenly becomes a very simple one:


See the difference? Instead of just assuming that everyone knows where I work and what mode of transportation I am looking for, if I make sure to simply tell them, there is no uncertainty, no confusion, no guesswork, and the question can be answered.  This isn't rocket science, right?  Yet, these common-sense basics seem to elude many, many, people!

Would you drop your car off at a mechanic with a note on it that says:


I sure hope not.  You'd explain what's wrong, right?  It has trouble starting, it has a flat tire, there's smoke coming from beneath the hood, the steering wheel fell off, and so on. It's basic common sense that you would do everything you can to be sure that the mechanic has the information he needs to fix your car correctly and promptly, right?  Shouldn't that same logic also apply when asking for help in forums?

Finally, let's try a SQL Server question:


Take a look at that question.  It is a very simple SQL statement, right?  There's nothing there that a beginner could not understand.   Can you "spot" the problem and fix it? 

Hmmm ... maybe you can, maybe you can't.  You can't really be sure, can you?  At this point, we can all try to guess what the problem is.  What does "it does not work" even mean? Is "SalesNumbesr" a typo?  Should it be "SalesNumbers" ?  Is it returning too much data?  not enough data?  Incorrect data?  Is it generating an error?  And so on. 

We could spend all day trying to guess what the question is and provide answers to those guesses, but if the guesses are wrong, the answers won't be so helpful, will they?  On the previous question, what if you guessed that I work in Cleveland and provided directions for me to Fenway Park from there?  Would that be helpful to me? Probably not, right?  Most likely, it just wasted everyone's time.

So, getting back to the code .... what is wrong with it?  Well, in SQL terms, the answer is: NULL!  It does not exist.  Until we are provided with more information, the question cannot be answered. Thus, it is not a question at all, just an incomplete fragment.  As simple as the question looks, as basic as the T-SQL is, this "question" will stump even the greatest "experts" out there because an answer to this question simply does not exist.

If more information is provided, like this:



Suddenly, the question now is very clear and the answer is very simple!  They just want to know how to add an expression to the result set. Just by providing a little more information, and not assuming that everyone knows what is happening outside of the context of what was written, something that was impossible to answer has become very easy. 

I sure hope this is making sense.

In fact, taking this whole article to its logical conclusion, I think we can safely say:


In other words, a vague, incomplete question can only get, at best, vague, incomplete answers.  But a question that spells out the entire situation very clearly will get, at best, a very clear and specific answer that works in that situation. 

So, please, think of this when you ask questions in a forum.  Consider the fact that no one knows your specific environment, or code, or application, or database except for YOU.  And, no one can help you unless you are providing enough information for them to do so. 

Just like the mechanic. Or someone giving driving directions.  Or a doctor when you are sick.   You provide them with the necessary information so they can help you, right?  Consider doing the same to those providing you with (free!) programming advice.

. . .

(Feel free to provide this link to those who seen to have trouble understanding that you cannot read their mind when assisting them with programming help on forums.)

posted @ Tuesday, May 13, 2008 11:04 AM | Feedback (6)

GROUP BY ALL

Here's an obscure piece of SQL you may not be aware of:  The "ALL" option when using a GROUP BY.

Consider the following table:



Suppose we'd like to see the customers that were sold Product #1 along with the total amount that they spent.

We would basically write a simple SELECT with a GROUP BY like this:


And sure enough, we'd get our answer:


Now, let's say that we'd like to see all customers that have been sold any products, but we still just want to see the "TotalAmount" for ProductID #1.  For customers that have never ordered ProductID #1, it should output a "TotalAmount" value of $0.   One way to do this is with a CASE expression; instead of filtering so that only ProductID #1 is returned, we can conditionally SUM() the Amount only for orders for ProductID #1.  Like this:


That gives us the results we want.   Because we are not using a WHERE clause to filter the data, we see an entry for CustomerID #2 in the output. 

However, in situations where you have written the above SQL, you could actually replace the SUM(CASE...) expression by using GROUP BY ALL, instead of just a standard GROUP BY, like this:


Notice that now all Customers are now returned, and a NULL is shown as the TotalAmount for Customer #2, who has no orders for ProductID #1 ...  Even though though the WHERE clause seems to indicate that we should not be seeing customer #2 in the results!

The ALL option basically says "ignore the WHERE clause when doing the GROUPING, but still apply it for any aggregate functions".   So, in this case, the WHERE clause is not considered when generating the population of CustomerID values, but it is applied when calculating the SUM.  This is very much like our first solution, where we removed the WHERE clause completely, and used a SUM(CASE...) expression to conditionally calculate the aggregate. 

Values that are excluded from the aggregation according to the WHERE clause have NULL values returned, as you can see in the result.  A simple ISNULL() or COALESCE() will allow us to return 0 instead of NULL:


Notice that the warning about NULL being aggregated still displays, since that is the standard behavior in SQL Server when you calculate an aggregate on a NULL value.  You can turn these warnings off if you like for the during of the batch by issuing a set ANSI_WARNINGS off command before your SELECT.

GROUP BY ALL is kind of obscure and neat to know, but not really useful in most situations since there are usually easier or better ways to get this result.  For one thing, this won't work if we want all Customers to be displayed, since a customer must have at least one order to show up in the result.  If we want to see all customers, even those that have never ordered, we would need to do a LEFT OUTER JOIN from the Customers table to our Orders aggregate SELECT:


That is typically the standard way to return data for an entire population, regardless of existing transactions.  GROUP BY ALL gets us close, but if a new customer has never made an Order, they will never show up in the results.   Of course, depending on your needs, that may be what you want.

Another limitation is we can not use GROUP BY ALL if we want to return a grand total for all orders, along with the total just for ProductID #1.  For example, using the SUM(CASE...) expression along with a regular SUM(), we can do this:


That lets us calculate two different totals all in one pass through the table.  However, we cannot translate that using GROUP BY ALL, because while we will be able to return the Product1Amount, there would be no easy way to also get the TotalAmount for all products without an additional join or sub-query.

. . .

So, that's the story with GROUP BY ALL. It is interesting, and not widely well-known, and may even make for a good interview question if you really want to see how much SQL a candidate knows.  But for practical purposes, it is pretty rarely used and there are generally better ways to get the same results more easily or more efficiently.

Anyone have a good situation or an example of where GROUP BY ALL really worked well for you?  Be sure to share your experiences in the comments.

posted @ Monday, May 05, 2008 12:25 PM | Feedback (1)

UNPIVOT: Normalizing data on the fly

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.  We all know that there's lots of bad databases designs out there, so this can be a handy technique to know. 

Of course, even a well designed, fully normalized database can still benefit from "unpivoting" from time to time, so let's take a look at some common situations and some of the options we have to handle this at our disposal.  We will focus on some traditional SQL techniques to do this, and then take a close look at the UNPIVOT operator that was introduced with SQL Server 2005.

Example #1:  A Bad database design

Let's start with a commonly bad table design, in which someone has decided to relate a client to multiple contacts by designing their client table like this:


(Note: For brevity, I am not including the contact table here, nor the foreign key constraints.  Of course, with this table design, it would probably be pretty unlikely to find such constraints in the database anyway)

With this design, it is not very easy or efficient to get a count of all contacts for each client, or to find out which contacts are related to which clients.   One thing we can do, however is to "unpivot" this table in a query that returns 1 row per ClientID/ContactID combination.  With that result set, we can easily now reference the table as if it were normalized and we can get the information we need.

One way to do is to use UNION ALL to return each row in the clients table 4 times, and each time return a different contactID column:

Another option is to CROSS JOIN the Clients table with a table or resultset that returns 4 rows, which also effectively returns each row in the clients table 4 times.  For each of the 4 values in the table we are cross joining, we grab a different contact column:

(Note that you can use a permanent table of Numbers in your database instead of generating it on the fly with a UNION, as shown)

Finally, however, there is an even eaiser way to handle this: the UNPIVOT operator, new with SQL 2005.  UNPIVOT works very efficiently and really allows you to handle this exact situation quite easily:


Much shorter to write, and more efficient to execute as well. 

Taking a Closer Look at UNPIVOT

The UNPIVOT operator is tricky to get a feel for, however, so let's take a look at it.


First, the "As Contact" at the end is just labeling the entire unpivot result set with an alias, just as you must alias a derived table.  Each column returned by the pivot operator can be referenced by the alias if necessary.


The "ContactID for" part says that we want to return a column called "ContactID" for each unpivoted row.  The IN() list is the columns that we are unpivoting; the values in the 4 columns listed here will be assigned to the ContactID column in the result.  So, the first time a particular row is unpivoted, the value of the 'contact1' column is assigned to ContactID, the next time it is the 'contact2' column, then 'contact3', and then finally 'contact4'.  Then, the next row is processed and it all begins again. 

Thus, because we are unpivoting 4 values, we know that the result of the unpivot will have 4 times as many rows as the source data.


UNPIVOT returns an additional column as well, which contains the name of the column that was used to produce each unpivoted row.   Here, we have specified that to be called ContactNumber.  Note that we actually did not return ContactNumber in our example, be we can easily add that in so you can see how it works:


So, you can see that the code to write is very short, but a little difficult to grasp at first.  In the end, though, we are able to take a bad table design and easily "fix it", at least temporarily, so that we can query it using simple and standard SQL statements to get what we need.

Example #2:  Normalizing a Transaction Table

Here's another common example:



Notice that we have different columns for Budget, Actual and Projection, which is not really a great database design.  Much better would be to break this data out so that we have a single 'Amount' column and a 'TransactionType' column that specifies the type of each transaction.  We can transform our Transactions tables into this format using UNION ALL:


Or, we can use the UNPIVOT operator to do the same much easier:


Example #3: "Unsummarizing" Data

For those who work with accounting systems, this example may be familiar to you.   Many times, Accounting systems have "summary" tables that roll up transactional data into a structure like this:


(As before, let's not worry about those foreign key constraints)

These tables are often calculated when transactions are posted, or periods are closed.   Typically, many reports pull from these tables because it is much more efficient than summarizing thousands or millions of transactions, and the data is already "cross-tabbed" the way most reporting tools would like to display it.

We can take this summarized data and "unpivot" it so we can still access the summarized data, but now it will be in a normalized structure.  All it takes is a simple UNPIVOT like this:


We can filter so that only periods with a non-zero amount are included, and we can SELECT FROM this result set and get the exact data we need for whatever date range we want without worrying which column the actual data is in.

This can also be done with a CROSS JOIN or a UNION ALL, but with 12 values to pivot, those options become much longer to write and UNPIVOT appears to be the way to go in this case.

Example #4: Multiple unpivots

Finally, let's consider a more complicated example.  Here, we have a table that stores games played between two teams, where one team is the HomeTeam and the other is the AwayTeam:


That may or may not be the best possible design for the Games table, but it is a common way to do it.  With data in that form, if we want to get the total runs scored per team across all games, or get each teams won-loss record, we need to make two passes through the table. This can be done fairly easily and efficiently with a union:


Now, how can we do this with UNPIVOT?  This example is a bit more complicated, because we are "unpivoting" not only the score, but also the TeamCode, and we are also calculating a few extra columns (Win, Loss and Tie).

So, can we UNPIVOT more than 1 column?  Let's start simply and do things one at a time.  First, let's UNPIVOT just the TeamCode:


OK, so far so good.  Now, how do we get the score for the team as well?  Let's add another UNPIVOT clause to the SELECT, this time for Score:


Hmmm. OK, we cannot specify the same column for both pivots.  That is just an alias specification, no big deal, so let's just alias it as "HomeOrAway2" :


Holy schnikies, it works!  We can specify more than one UNPIVOT clause for the same SQL statement!  Who would have thunk it?

Uh oh -- wait a second.  We have 24 results returned.  We should have only 12.  Something is not right here.  Remember when we aliased our second unpivot column as "HomeOrAway2"?  We did not return that anywhere in our results.  Let's add that in and take a look:


Notice what is happening here. Without an UNPIVOT, we had 6 rows.  We added one UNPIVOT to pivot two values, and got 12 rows.  Then, we added another pivot, again with two values, and got back 24.  Looking at the results, we can see that the single game at '2008-04-01' was unpivoted into 4 rows.  So, while we can unpivot multiple times, there is no relation between each UNPIVOT.  That is, notice there is no correlation between HomeOrAway and HomeOrAway2.  Let's look at just that first game:


Both scores, home and away, are specified for each team -- even if it is not their matching score.  What we really want is only these two lines:

How can we get there?  Well, there's always a WHERE clause!  We can simply filter our result so that the Left 4 characters of HomeOrAway matches the left 4 characters of HomeOrAway2.  After all, there is nothing magical about these unpivoted columns, they are data just like anything else.

So, we can now write:

Now we are back to 12 results returned, the Homes and Aways match up, and things look good!  Hey, we just pulled off 2 unpivots at once, not bad!  From here, we can add our Win, Loss and Tie calculations as well.  Note that we also have removed "HomeOrAway2" from the SELECT, since we don't need to see that column in our result.  Finally, we are returning only the left 4 characters of HomeTeam or AwayTeam, so that the word 'Home' or 'Away' is returned in the HomeOrAway column.

This seems like quite a bit of work.  Is UNPIVOT really the way to go in this scenario?  Maybe not.  I have not done full performance testing of UNPIVOT versus the other options shown (this article is way too long as it is!) but as always you should fully test your options and decide which offers the best performance and code simplicity.

Another option if you want to unpivot more than 1 column is to just use one UNPIVOT cause, but then use CASE expressions to return the values you need.  For example: