Heh.  Well I forgot to post the best version of the XML aggregator when I posted the other day.  Then Ward Pond tagged in a post on it (here), and pointed out that I was "improving" on an old version that had been fixed by Adam Machanic among others...

Here is my "good" version that fixes the entitization problem:

SELECT (

SELECT n + ','
FROM (

  SELECT 'a<b' AS n
UNION ALL
  SELECT 'b>a'
UNION ALL
  SELECT 'b&a'
UNION ALL
  SELECT 'b

a') r

FOR XML PATH(''), TYPE

).value('.[1]','varchar(max)')
-- =====

 And here is the version currenlty being used by Ward and company...:

;WITH ColumnToPivot ([data()]) AS (
    SELECT p.ParentString + N', '
    FROM Parent p
    JOIN Child c
    ON c.ParentId = p.ParentId
    WHERE c.ChildId = 2
    ORDER BY p.ParentId
    FOR XML PATH(''), TYPE
),
    XmlRawData (CSVString) AS (
        SELECT (SELECT [data()] AS mydata FROM ColumnToPivot AS d FOR XML RAW, TYPE).value( '/row[1]/mydata[1]', 'NVARCHAR(max)') AS CSV_Column
)
SELECT LEFT(CSVString, LEN(CSVString)-1) AS CSVList
FROM XmlRawData

Finally, I heard yesterday that Adam is has a new challenge, involving, you guessed it, grouping concatenated strings.  Guess I better get busy...

Just a quick note ...

Upon further testing and analysis, I feel confident that the best solution for string concatenation in SQL Server 2005+ is the FOR XML method, like so:

SELECT CAST(
   (SELECT TABLE_NAME+', '
     From INFORMATION_SCHEMA.TABLES
     Order By TABLE_NAME
     FOR XML PATH('')
) as VARCHAR(MAX))

It has a lot of advantages, which I will go into later.  It also has one big problem(Entitization) which I will also demonstrate how to fix...

A fast and accurate way to calculate the distance in miles between two points, based on the latitudes and longitudes.

I've been thinking about writing an article that would be titled "15 Ways to Leave Your Cusors".  Ideas:

1. Just put it in a Set, Brett  (change the declare cursor select statement  into a select)
2. Put it all together, Heather (later one; using women's names opens this  up a lot too)
3. Put it on the Stack, Jack (recursive CTE's)
4. Its your last resort Mort (or While/Kyle?)
5. Select into a variable, Aribel (simple pseudocursor)
6. Use an ordered update, Nate (complex psuedocursor)
7. Window on OVER, Grover (Row_Number(), etc.)
8. Add a WITH CUBE, Noob (Rollup and Cube)
9. Use a temproary table, Mabel
10. Just begin again, Man (last one; start over with just specs)
11. With OUTPUT (for loops with multiple outputs; no cute rhyme yet)
12. Make a big string, Bing (dynamic SQL)
13. bring it inline, Caroline
14. ...?

Other ideas: Joins, Exists, IN(), derived tables, Merge(?)

Armand Posted this cool function to tell if a specific year is a leap year or not at www.mssqltips.com/tip.asp :