I’m finally back from my vacation. Tunisia’s great: dates, Carthage, sea and stuff.
Now, to the questions.
Create a table called
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).
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)
|1||X, Y, Z|
|2||A, B, C|
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:
- Assign a group-wise row number and a group-wise count to each record (in required order)
- Select the first record (that with the row number 1) from each group in the anchor part of the
- Recursively append the next record to the previous record. The next record can be obtained by joining on
rn = rn + 1
- 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)
|2||A, B, C|
|1||X, Y, Z|
As we can see, this only deals with native
NVARCHAR and is free from
Hope that helps.
I’m always glad to answer the questions regarding database queries.