Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

Database Column Names != Report Headings

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 →

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. Read more →

Creating CSV strings in SQL: Should Concatenation and Formatting Be Done at the Database Layer?

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 →

How to be an Effective Report Writer

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 →