Subscribe to my RSS feed
|
|
SQL Server 2005 "COALESCE()" Function
The COALESCE() function in SQL Server 2005 is cool. Say you have a database schema as follows (primary keys highlighted in bold):
Widget
widgetId
widgetName
Category
categoryId
categoryName
WidgetCategories
widgetId
categoryId
According to the schema, one Widget could be in zero or more categories. Of course we can write queries to return rows of widgets, and we can write queries to return WidgetCategories for a specific Widget. But what if we wanted to select 1 specific widget by widgetId, and in that row have a column called "categories" that held a comma-separated list of categories that the widget is in? This might be useful for some kind of summary page where you would want to see all the categories a widget is in, in ONE QUERY.
Enter the COALESCE() function! Check out the SQL Function below and the SELECT query after it:
2 CREATE FUNCTION dbo.fWidgetCategories
3 (@widgetId int)
4 RETURNS varchar(5000) AS
5 BEGIN
6 DECLARE @list varchar(5000)
7 SELECT @list = COALESCE(@list + '', '','''') + Category.[categoryName]
8 FROM Category C INNER JOIN WidgetCategory WC
9 ON WC.widgetId = C.widgetId
10 WHERE WC.widgetId = @widgetId
11 RETURN @list
12 END
The SELECT query:
2 SELECT W.widgetName, dbo.fWidgetCategories(W.widgetId)
AS categories
3 FROM Widget W
These two things will accomplish exactly what we are looking for and return:
widgetName categories
widgetA sprockets, rockets
widgetB gizmos, gadgets
Very cool! I believe the COALESCE function is present in SQL Server 2000 also.
| Comments | Currently, there are no comments. Be the first to post one!
Click here to post a comment |
|