Stupid SQL Tricks

Although this is not directly related to BI, I thought I would share some T-SQL tricks that I've picked up over the years.  The reason I chose the title is because at first glance these SQL statements might not look like they would work or the reason they do work is not very intuitive.

Returning Values from Multiple Rows in a Single Row

Normally, if you want to combine values from multiple rows you would use a cursor or a loop.  With this trick, it can be done with a single SQL statement.  For example, if you run the following statement it returns multiple rows.

select [Name]
from HumanResources.Shift

 Returns:
Day
Evening
Night

 But making a few modifications will return a single row with all three values. 

declare @str varchar(100)

select @str = coalesce(@str, '') + [Name] + ', '
from HumanResources.Shift

select @str

Returns:
Day, Evening, Night

Calling ODBC Scalar Functions in T-SQL

This is something I recently discovered and had no idea was possible.  Although most of the ODBC scalar functions are duplicated in T-SQL, a few are not and might be useful in certain situations.

select {fn dayname('2007-04-21')};

Returns:
Saturday

select {fn concat( 'This is the first string ', 'and this is the second.')};

Returns:
This is the first string and this is the second.

Be careful when using these because the data type of the return value might not be what you expect.

Here's the link on MSDN:

ODBC Scalar Functions (Transact-SQL)

Creating a Custom Sort Order

Have you ever wanted to sort your results in an order other than ascending or descending?  I recently needed to do just that and after playing around with the CASE expression, here is what I came up with.

The following SELECT statement sorts all male titles first followed by female titles and any other values.

select Title, FirstName, LastName, ContactID
from Person.Contact
where LastName like 'A[a-c]%'
order by case Title
           when 'Mr.' then 1
           when 'Sr.' then 2
           when 'Sra.' then 3
           when 'Ms.' then 4
           else 5
         end, LastName, FirstName

NOTE:  The CASE expression must include all possible values for the sort column or include an ELSE clause.  Otherwise, it will not work.

Well, that's it for now.  Let me know what you think or if you have any of your own Stupid SQL Tricks.  If I collect enough of them, I'll post another blog entry.