Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

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 →

Criteria on Outer Joined Tables

As this nice SQLTeam article explains, when using an OUTER JOIN, you should put criteria on the outer table in the join condition, not in the WHERE clause. However, I often see a "workaround" to avoid this simple and solid rule, which might seem to work but actually doesn't. Read more →