dBforums - View Single Post

Peter.Vanroose · #14 (**permalink**) 09-29-08, 04:12

Here is a variant on the above, to concatenate the fields in column "v", grouped by the column "g", but in the order specified by column "s".

The table

Code:

items.g items.v items.s
10      a       2
10      b       1
20      b       7
20      c       700
20      d       70

will be transformed into

Code:

g  list
10 b, a
20 b, d, c

Code:

WITH t(g, s, list) AS
( SELECT g, s, CAST(RTRIM(v) AS VARCHAR(254))
  FROM   items
 UNION ALL
  SELECT t.g, items.s, t.list || ', ' ||RTRIM(items.v)
  FROM   t INNER JOIN items i ON t.g = i.g
  WHERE  LOCATE(RTRIM(items.v),t.list) = 0
    AND  i.s > t.s
    AND  NOT EXISTS (SELECT 1 FROM items
                     WHERE  g = i.g
                       AND  s > i.s
                       AND  s < t.s)
)
, l(g, len) AS
( SELECT g, MAX(LENGTH(list))
  FROM   t
  GROUP BY g )
SELECT t.g, t.list
FROM   t INNER JOIN l  ON  l.g = t.g AND l.len = LENGTH(t.list)

or (maybe slightly more performant):

Code:

WITH t(g, s, list) AS
( SELECT g, s, CAST(RTRIM(v) AS VARCHAR(254))
  FROM   items
 UNION ALL
  SELECT t.g, items.s, t.list || ', ' ||RTRIM(items.v)
  FROM   t INNER JOIN items i ON t.g = i.g
  WHERE  LOCATE(RTRIM(items.v),t.list) = 0
    AND  i.s = (SELECT MIN(s) FROM items
                WHERE  g = i.g
                  AND  s > t.s)
)
, l(g, len) AS
( SELECT g, MAX(LENGTH(list))
  FROM   t
  GROUP BY g )
SELECT t.g, t.list
FROM   t INNER JOIN l  ON  l.g = t.g AND l.len = LENGTH(t.list)