Comparing Rows in a Table

As all SQL professionals will know sqlservercentral.com is an amazing resource. In an entry titled Linking to the Previous Row the author, David McKinney, describes a simple way to compare rows within a table using the two functions introduced in SQL Server 2005: Common Table Expressions (CTE) and Rownumber().

To be honest, until I read this article, I didn't see much of a use for CTEs other than recursive queries (for more on that see: Recursive Queries in SQL Server 2005). But this example provides a tidy method for comparing, for instance, changes over time.

And if you don't already know, the Rownumber() function allows you to include, as the name suggests, a column of row numbers in your result set based on a sort order that you determine. In addition, the PARTITION BY allows you to group the results.

I will definitely be employing this

Parse a Comma Separated List

Here is a simple user-defined function to parse a comma separated (or any other character separated) list and return a table of the values in the list. The input variable must be a text data type (varchar). It returns the results as varchar so depending on your needs you may need to convert to int or datetime, etc.