SQL Concatenation with the STUFF function and XML PATH clause

Recently I had the necessity to retrieve a concatenated string of values as a column for a report.  This can be achieved quite simply by creating a UDF that returns the concatenated string and calling within the SELECT statement.

However, I do not like using UDF’s within SELECT statements due to performance issues.  So I started searching the net for a possible alternative and came across the following:

http://www.kodyaz.com/articles/concatenate-using-xml-path.aspx

Now I have never used the SQL STUFF function or XML Path clause but the combination of the two enabled me to achieve my goal very quickly.

The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

You can execute SQL queries to return results as XML instead of standard rowsets. These queries can be executed directly or executed from within stored procedures and user-defined functions. To retrieve the results directly, first use the FOR XML clause of the SELECT statement.

SELECT TOP 1
  STUFF(
            (
                SELECT ', ' + Username
                FROM Users
                FOR XML PATH('')
            ), 1, 1, ''
        ) As ListOfUsers
From Users

Read rest of entry

An Introduction to Using Patterns in Web Design

A fantastic article from Ryan Singer at 37 Signals.

http://37signals.com/papers/introtopatterns/index

The use of design patterns in web design can help developers tackle the tricky balance between user experience and business objectives.

Read rest of entry

SQL: OVER Clause

The OVER clause allows partitioning and ordering of the rowset.

The syntax for this function:

Ranking Window Functions

< OVER_CLAUSE > :: =

    OVER ( [ PARTITION BY value_expression, ... [ n ] ]

           <ORDER BY_Clause> )Aggregate Window Functions

< OVER_CLAUSE > :: =

    OVER ( [ PARTITION BY value_expression, ... [ n ] ] )

As specified above the OVER clause can be used in conjunction with aggregate functions, which can make your code a little shorter.

For example, consider the following bookings table:

SELECT * FROM #Bookings ORDER BY CourseID

CourseID	DelegateID	BookingDate	BookingCost
1	1	2011-02-04	10.00
1	2	2011-02-04	10.00
2	1	2011-01-04	10.00
3	1	2011-03-04	50.00
3	2	2011-03-04	50.00

Now it is quite simple to return the total cost per delegate by using the SUM aggregate function with the OVER clause.

SELECT CourseID, DelegateID, BookingDate, BookingCost, SUM(BookingCost) OVER (PARTITION BY DelegateID) AS TotalCost FROM #Bookings

CourseID	DelegateID	BookingDate	BookingCost	TotalCost
1	1	2011-02-04	10.00	70.00
2	1	2011-01-04	10.00	70.00
3	1	2011-03-04	50.00	70.00
1	2	2011-02-04	10.00	60.00
3	2	2011-03-04	50.00	60.00

An alternative SQL sctipt without using the OVER clause would be:

SELECT CourseID, DelegateID, BookingDate, BookingCost,
    (SELECT SUM(BookingCost) FROM #Bookings BK WHERE BK.DelegateID = #Bookings.DelegateID) AS TotalCost
FROM #Bookings     

Read rest of entry

jQuery 1.5 Released

jQuery 1.5 was released for consumption today!

Grab jQuery 1.5 here

This release includes:

• Complete rewrite of the Ajax module.
• New feature: Deferred Objects
• New method: jQuery.sub()
• Performance improvements (Adjacent Traversal)
• Bug Fixes - Approx 83

Read rest of entry