Hello everyone!  Let's talk SQL.  My names Jonathan and today I have a useful tip for a new view on queries.  Generally when someone queries two database tables that share an identity column, they join the data and end up with a collection of rows reflecting each result, essentially creating a "combined key".  To show this example more simply, take a look at the example below:

view plain print about
1SELECT c.client_id, s.sale_id
2FROM clients c
3    INNER JOIN sales s ON s.client_id = c.client_id

client_id

 sale_id
1 2
3 3
3 4
4 5
3 6
1 7

Fig-1


This query outputs a "client sales" (See Fig-1) result set that has 7 rows.  In this example such a small result set is hardly a problems, but let us imagaine that we have hundreds of clients, each with thousands of sales under their belt.  This result set would suddenly be very daunting.  Post-processing the result in your choice web service would be a performance headache, as to output it someone would have to either do a query of a query to show which clients have which sales, or this would have to be broken into a single cleint table query, and then individual queries  in a loop on the web server end.

Wouldn't it be great if there was a way to greatly reduce the row count, the number of queries, and the amount of processing on the web server.  The solution below does exactly that. 

view plain print about
1SELECT c.client_id, left(sl.saleslist,len(sl.saleslist)-1) AS sale_list
2FROM clients c
3cross apply (
4                SELECT CAST(s.sale_id AS varchar) + ','
5                FROM sales s
6                WHERE s.client_id = c.client_id
7                for xml path('')
8            )sl(saleslist)

client_id

 sale_id
1 2,7
3 NULL
3 3,4,6
4 5

Fig-2


The query above casts an nodeless XML result set into a varchar field and delimits it with comma's, creating a list (See Fig-2).  The last character is removed in the main select clause otherwise the last character would be a comma.

That's it!  You'll now be able to utilize new new field (in this case sale_list) in any list functions you may have. 

This entry was posted on February 25, 2009 at 5:13 PM and has received 374 views. There are currently 0 comments. Print this entry.