CODEWITHSUNDEEP
sqlpostgresql
Shadi Ziedan
Shadi Ziedan

Reputation: 43

How to merge sequenced duplicate values inside string_agg

I have a table like the following:

create table path(
  id integer,
  start varchar(255),
  finish varchar(255),
  client integer

);

INSERT INTO path(id, start, finish, client) VALUES (1, '1', '2', 1);
INSERT INTO path(id, start, finish, client) VALUES (2, '2', '3', 1);
INSERT INTO path(id, start, finish, client) VALUES (3, '3', '4', 1);
INSERT INTO path(id, start, finish, client) VALUES (4, '5', '6', 1);

I've tried the following query:

select string_agg(start || ' -> ' ||finish, ', ' order by id) from path group by client;

and the result was like that:

1 -> 2, 2 -> 3, 3 -> 4, 5 -> 6

How can I get the following result if two sequenced values are the same:

1 -> 2 -> 3 -> 4, 5 -> 6

Here's the Sql Fiddle link.

Upvotes: 0

Views: 83

Answers (1)

JNevill
JNevill

Reputation: 50034

As mentioned in my comment, a recursive CTE will produce this. As an example:

WITH RECURSIVE reccte AS
(
  /*Recursive Seed - Records that will start recursion*/
  SELECT id, start, finish, client, start || '->' || finish as path, 1 as depth
  FROM path
  WHERE id = 1
  UNION ALL
  /*Recursive Term - This will be the statement that iterates until the join fails*/
  SELECT
    path.id, path.start, path.finish, path.client, reccte.path || '->' || path.finish, depth + 1
  FROM reccte 
    INNER JOIN path ON reccte.finish = path.start
  WHERE depth < 20 /*make sure we don't loop endlessly*/
)
SELECT * FROM reccte;


+----+-------+--------+--------+---------------+-------+
| id | start | finish | client |     path      | depth |
+----+-------+--------+--------+---------------+-------+
|  1 |     1 |      2 |      1 | 1->2          |     1 |
|  2 |     2 |      3 |      1 | 1->2->3       |     2 |
|  3 |     3 |      4 |      1 | 1->2->3->4    |     3 |
|  4 |     4 |      5 |      1 | 1->2->3->4->5 |     4 |
+----+-------+--------+--------+---------------+-------+

That final SELECT can be changed out to SELECT path FROM reccte ORDER BY depth DESC LIMIT 1; in this example to go after the record you want.

UPDATED:

The solution is still the same here, we just tweak the recursive CTE and final select to get the different full branches/path for your hierarchy:

http://sqlfiddle.com/#!17/2892f/6

WITH RECURSIVE reccte AS
(
  SELECT id as origin, id, start, finish, client, start || ' -> ' || finish as path, 1 as depth
  FROM path
  WHERE start NOT IN (SELECT distinct finish FROM path)
  UNION ALL
  SELECT
    reccte.origin, path.id, path.start, path.finish, path.client, reccte.path || ' -> ' || path.finish, depth + 1
  FROM reccte 
    INNER JOIN path ON reccte.finish = path.start
  WHERE depth < 20 /*make sure we don't loop endlessly*/
)
SELECT * FROM (SELECT origin, path, depth, max(depth) OVER (PARTITION BY origin) as maxdepth FROM reccte) sub WHERE sub.maxdepth = depth;


+--------+------------------+-------+----------+
| origin |       path       | depth | maxdepth |
+--------+------------------+-------+----------+
|      1 | 1 -> 2 -> 3 -> 4 |     3 |        3 |
|      4 | 5 -> 6           |     1 |        1 |
+--------+------------------+-------+----------+

You can string_agg() here too in that final select to get EXACTLY the output in your updated question:

http://sqlfiddle.com/#!17/2892f/5

SELECT STRING_AGG(path, ', ' order by origin) FROM (SELECT origin, path, depth, max(depth) OVER (PARTITION BY origin) as maxdepth FROM reccte) sub WHERE sub.maxdepth = depth;

+--------------------------+
|        string_agg        |
+--------------------------+
| 1 -> 2 -> 3 -> 4, 5 -> 6 |
+--------------------------+

Upvotes: 1

Related Questions