The SQL Wizard

Here is a good example of the power of Common Table Expressions in SQL Server 2005. This feature has saved me many times by providing clean solutions to somewhat complex SQL problems.

The example starts out by declaring a table variable and inserting several names and genders into the table. Now, suppose you have to generate a report which groups each individual by gender, and displays them in a comma separated list. This can be done quite easily by using a recursive Common Table Expression.

Before we generate the names list, we need to give each row an identifier so we can keep track where we are when recursively calling the CTE. This is accomplished by using the ROW_NUMBER() function which is implemented below in the "NAMES_RANKED" CTE.

After this, the "GENERATE_NAMES_LIST" CTE begins by defining the initial value followed by a UNION ALL statement. The next query in the CTE recursively calls the "GENERATE_NAMES_LIST" CTE to concatenate the current value with the previous value. By itself, this CTE would produce the following results:

Male 1 Bob
Male 2 Bob, Dave
Male 3 Bob, Dave, Jack
Male 4 Bob, Dave, Jack, Tim
Female 1 Amber
Female 2 Amber, Jane
Female 3 Amber, Jane, Sue

As you can see, the values are accumulated with each call to the CTE. All we have to do now is determine the highest ranked for each gender, and display the values. This is accomplished by the CTE "MAX_RANK" and the final query.

Go crazy with this!

DECLARE @Names TABLE(FirstName VARCHAR(30), Gender VARCHAR(10))

INSERT INTO @Names VALUES('Bob','Male')
INSERT INTO @Names VALUES('Dave','Male')
INSERT INTO @Names VALUES('Jack','Male')
INSERT INTO @Names VALUES('Tim','Male')
INSERT INTO @Names VALUES('Jane','Female')
INSERT INTO @Names VALUES('Amber','Female')
INSERT INTO @Names VALUES('Sue','Female');

WITH NAMES_RANKED AS
(
    SELECT FirstName
        ,Gender
        ,ROW_NUMBER() OVER(PARTITION BY Gender ORDER BY FirstName) RowNumber
    FROM @Names
),
GENERATE_NAMES_LIST(Gender, RowNumber, NameList) AS
(
    SELECT Gender
        ,RowNumber
        ,CAST(FirstName AS VARCHAR(MAX))
    FROM NAMES_RANKED
    WHERE RowNumber = 1
    UNION ALL
    SELECT n.Gender
        ,n.RowNumber
        ,CAST(g.NameList + ', ' + n.FirstName AS VARCHAR(MAX))
    FROM GENERATE_NAMES_LIST g
        INNER JOIN NAMES_RANKED n ON n.Gender = g.Gender
            AND n.RowNumber = g.RowNumber + 1
),
MAX_RANK AS
(
    SELECT Gender
        ,MAX(RowNumber) MaxRank
    FROM NAMES_RANKED
    GROUP BY Gender
)
SELECT g.Gender
    ,NameList
FROM GENERATE_NAMES_LIST g
    INNER JOIN MAX_RANK m ON m.Gender = g.Gender
        AND m.MaxRank = g.RowNumber