recursive CTE to combine values

Question

Please note, this is not a duplicate of the questions cited in the comments, as this requires a CTE.

How do you create a recursive Common Table Expression which combines grouped data (like string_agg() or group_concat())?

For example, I have a simple data set like the following:

┌──────┬──────────┐
│ code │ category │
╞══════╪══════════╡
│ 1    │ A        │
├──────┼──────────┤
│ 1    │ B        │
├──────┼──────────┤
│ 2    │ A        │
├──────┼──────────┤
│ 3    │ B        │
├──────┼──────────┤
│ 4    │ B        │
├──────┼──────────┤
│ 4    │ C        │
├──────┼──────────┤
│ 4    │ D        │
├──────┼──────────┤
│ 5    │ B        │
└──────┴──────────┘

I would like to generate a result set which combines the categories by group as follows:

┌──────┬──────────┐
│ code │ category │
╞══════╪══════════╡
│ 1    │ A,B      │
├──────┼──────────┤
│ 2    │ A        │
├──────┼──────────┤
│ 3    │ B        │
├──────┼──────────┤
│ 4    │ B,C,D    │
├──────┼──────────┤
│ 5    │ B        │
└──────┴──────────┘

I think the answer is probably generic, but I am particularly interested in finding a solution for PostgreSQL, SQLite and, and SQL Server.

The answer is most certainly not generic or ANSI standard, and you don't need recursive CTE to answer this question. Tag with the database you are actually using. — Tim Biegeleisen, Sep 15 '17 at 02:12
Base on the sample and result, it look like concat row values... [Postgresql](https://stackoverflow.com/questions/15847173/concatenate-multiple-result-rows-of-one-column-into-one-group-by-another-column), [SQLite](https://stackoverflow.com/questions/3926162/group-different-rows-in-one-by-combining-strings) and [SQL Server](https://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) — Prisoner, Sep 15 '17 at 02:15
@Anagha No, it’s not a duplicate. I am looking for a solution using a CTE. The other answers don’t. — Manngo, Sep 15 '17 at 04:35

score 0 · Answer 1 · answered Sep 15 '17 at 07:23

With sufficient thrust, pigs fly just fine. However, this is not necessarily a good idea.
— RFC 1925

First, get all distinct code values. Then determine the first (smallest) category value for each code. Then, in the recursion, get the next-larger category value for each code. The result consists of all rows that did the latest step for each code:

WITH RECURSIVE OinkOink(code, category, combined, step) AS (
  SELECT code,
         (SELECT MIN(MyTable.category)
          FROM MyTable
          WHERE MyTable.code = DistinctCodes.code),
         (SELECT MIN(MyTable.category)
          FROM MyTable
          WHERE MyTable.code = DistinctCodes.code),
         1
  FROM (SELECT DISTINCT code
        FROM MyTable) AS DistinctCodes

  UNION ALL

  SELECT code,
         (SELECT MIN(MyTable.category)
          FROM MyTable
          WHERE MyTable.code     = OinkOink.code
            AND MyTable.category > OinkOink.category),
         combined || ',' ||
           (SELECT MIN(MyTable.category)
            FROM MyTable
            WHERE MyTable.code     = OinkOink.code
              AND MyTable.category > OinkOink.category),
         step + 1
  FROM OinkOink
  WHERE EXISTS (SELECT *
                FROM MyTable
                WHERE MyTable.code     = OinkOink.code
                  AND MyTable.category > OinkOink.category)
)
SELECT code, combined
FROM OinkOink
JOIN (SELECT code,
             MAX(step) AS step
      FROM OinkOink
      GROUP BY code) AS LargestSteps
USING (code, step)
ORDER BY code;

recursive CTE to combine values

1 Answers1

Linked