I recently had the task of creating a report where they wanted one of the columns to list out all the users associated with a given report line item in a single comma separated string. My initial thoughts were, that this would involve some type of cursor or at least a loop of some description, which I'd then have to attach to the main result set. However, it seems there's a very nice little trick you can use that will solve this issue without the use of cursors or loops!
With the use of the COALESCE keyword you can create a list in a single line of SQL (except for declarations). I'll use the AdventureWorks database to demonstrate the technique. Assume you need to get a list of Countries and the regions they contain, in a report similar to the one below: More...
Currently rated 3.1 by 22 people
- Currently 3.136364/5 Stars.
- 1
- 2
- 3
- 4
- 5