Many times it is needed for reporting purposes to summarize normalized data into groups or lists of values. This is also known as rows concatenation. Some reporting and client side tools support this directly. Here is one approach to solve this in T-SQL using FOR XML PATH. CREATE TABLE Products ( sku INT PRIMARY KEY, product_desc VARCHAR(35)); CREATE […]
The new ranking functions in SQL Server 2005 have great impact on how problems can be solved. Here is just another example of that. Given a table with events at different venues (all event dates are unique), find the duration intervals of start/end date of uninterrupted performances at a given venue. Solving this without the […]
Performing updates on columns based on values from another table is a very common need. Using the ANSI UPDATE normally requires multiple subqueries, which can be very inefficient especially if multiple filters have to be applied. The Microsoft specific UPDATE with JOIN is one solution. However, common table expressions provide a very elegant alternative, which […]
Performing string manipulations is not the best use of T-SQL. But there are a lot of operations that can be done using pure SQL. Here is one example. Given an input string that has list elements separated by delimiter, slice the list into individual element, and provided index parameter return the matching item based on […]
An interesting problem to solve is finding relation paths in contact management systems. This is a limited case of the Dijkstra’s algorithm for finding the shortest path in a graph. Here we always have cost of 1 for each path and it is a two-way relation between the nodes. To put this in human readable […]
Although formatting output belongs to the reporting or front-end interface, occasionally there could be the need to change a column alias based on variable. Since T-SQL does not support proving variable as column alias in a query, here are two methods to handle that. CREATE TABLE Foo ( keycol INT PRIMARY KEY, datacol CHAR(1)) INSERT INTO Foo VALUES […]
Very often there is the need to check if a key value exists to perform an update, and if it does not exist to insert new data. The upcoming SQL Server 2008 provides the MERGE statement (MERGE actually allows to do more: simultaneous UPDATE, INSERT and/or DELETE operations on the table), but until it is […]
One of the new features of SQL Server 2008 is the support for table value constructors (part of ANSI SQL). Here are a couple quick examples of using them. — Populate sample table CREATE TABLE Foo ( keycol INT PRIMARY KEY, datacol VARCHAR(30)); In the past, populating table rows was done like this: INSERT INTO Foo […]
Calculating work hours is a very frequent need in scheduling, billing, payroll, and time and attendance applications. Normally this is best done using an auxiliary Calendar table which provides easy way for calculation and flexibility to account for holidays and other events. However, sometimes the requirements are simplified and the point of interest is only […]
A very frequent need is to define unique column that allows multiple NULL values. Since the UNIQUE constraint considers two NULL values the same, it allows only a single NULL value. Here is one solution for this problem using a view filtering on non NULL values with UNIQUE index on the view. CREATE TABLE dbo.Foo […]