posted 2/23/2010 by BillBrannen - Views: [2033]
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:DayEveningNight
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:Saturdayselect {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, ContactIDfrom Person.Contactwhere 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, FirstNameNOTE: 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.