EXPLAIN EXTENDED

I'm finally back from my vacation. Tunisia's great: dates, Carthage, sea and stuff.

Now, to the questions.

Mahen asks:

Create a table called Group:

Group

id	prodname
1	X
1	Y
1	Z
2	A
2	B
2	C

The resultset should look like this:

id	prodname
1	X,Y,Z
2	A,B,C

Can you please help me to solve the above problem using a recursive CTE?

This is out good old friend, GROUP_CONCAT. It's an aggregate function that returns all strings within a group, concatenated. It's somewhat different from the other aggregate functions, because, first, dealing with the concatenated string can be quite a tedious task for the groups with lots of records (large strings tend to overflow), and, second, the result depends on the order of the arguments (which is normally not the case for the aggregate functions). It's not a part of a standard SQL and as for now is implemented only by MySQL with some extra vendor-specific keywords (like ORDER BY within the argument list).

This functionality, however, is often asked for and I have written some articles about implementing this in PostgreSQL and Oracle.

Now, let's see how to do it in SQL Server.

Usually, SQL Server's FOR XML clause is exploited to concatenate the strings. To do this, we obtain a list of group identifiers and for each group, retrieve all it's product names with a subquery appended with FOR XML PATH(''). This makes a single XML column out of the recordset:

WITH    q (id, prodname) AS
        (
        SELECT  1, 'X'
        UNION ALL
        SELECT  1, 'Y'
        UNION ALL
        SELECT  1, 'Z'
        UNION ALL
        SELECT  2, 'A'
        UNION ALL
        SELECT  2, 'B'
        UNION ALL
        SELECT  2, 'C'
        )
SELECT  *
FROM    (
        SELECT  DISTINCT id
        FROM    q
        ) qo
CROSS APPLY
        (
        SELECT  CASE ROW_NUMBER() OVER(ORDER BY prodname) WHEN 1 THEN '' ELSE ', ' END + qi.prodname
        FROM    q qi
        WHERE   qi.id = qo.id
        ORDER BY
                prodname
        FOR XML PATH ('')
        ) qi(r)

This solution works, but converting to and from XML is not the best way to deal with the strings: things like ampersands, angle brackets, line feeds etc. get mangled and require some additional effort to cope with.

However, this functionality can really be implemented using a recursive CTE.

To do this, we need to do the following:

1. Assign a group-wise row number and a group-wise count to each record (in required order)
2. Select the first record (that with the row number 1) from each group in the anchor part of the CTE
3. Recursively append the next record to the previous record. The next record can be obtained by joining on rn = rn + 1
4. Finally, select the only last record from each group (whose row number is equal to the group-wise count). It will contain the accumulated string.

Here's how we do it:

WITH    q (id, prodname) AS
        (
        SELECT  1, 'X'
        UNION ALL
        SELECT  1, 'Y'
        UNION ALL
        SELECT  1, 'Z'
        UNION ALL
        SELECT  2, 'A'
        UNION ALL
        SELECT  2, 'B'
        UNION ALL
        SELECT  2, 'C'
        ),
        qs(id, prodname, rn, cnt) AS
        (
        SELECT  id, prodname,
                ROW_NUMBER() OVER (PARTITION BY id ORDER BY prodname),
                COUNT(*) OVER (PARTITION BY id)
        FROM    q
        ),
        t (id, prodname, gc, rn, cnt) AS
        (
        SELECT  id, prodname,
                CAST(prodname AS NVARCHAR(MAX)), rn, cnt
        FROM    qs
        WHERE   rn = 1
        UNION ALL
        SELECT  qs.id, qs.prodname,
                CAST(t.gc + ', ' + qs.prodname AS NVARCHAR(MAX)),
                qs.rn, qs.cnt
        FROM    t
        JOIN    qs
        ON      qs.id = t.id
                AND qs.rn = t.rn + 1
        )
SELECT  id, gc
FROM    t
WHERE   rn = cnt
OPTION (MAXRECURSION 0)

As we can see, this only deals with native NVARCHAR and is free from XML conversions.

Hope that helps.

I'm always glad to answer the questions regarding database queries.

Ask me a question