Shlomi Schwartz Shlomi Schwartz - 3 months ago 8
SQL Question

Reduce results into accumulated groups

Having the following table (describing a conversation):

id | record_id | is_response | text |
---+------------+---------------+----------------------+
1 | 1 | false | first line of text |
2 | 1 | true | second line of text |
3 | 1 | false | third line of text |
4 | 1 | true | fourth line of text |
5 | 1 | true | fifth line of text |
6 | 2 | false | first line of text |
7 | 2 | true | second line of text |
8 | 2 | false | third line of text |
9 | 2 | true | fourth line of text |
10 | 2 | true | fifth line of text |


I'm looking for an SQL query to output the following:

record_id | in_text | out_text
----------+-----------------------+---------------------
1 | first line of text | second line of text
----------+-----------------------+---------------------
1 | first line of text |
| second line of text |
| third line of text | fourth line of text
----------+-----------------------+---------------------
1 | first line of text |
| second line of text |
| third line of text |
| fourth line of text | fifth line of text
----------+-----------------------+---------------------
2 | first line of text | second line of text
----------+-----------------------+---------------------
2 | first line of text |
| second line of text |
| third line of text | fourth line of text
----------+-----------------------+---------------------
2 | first line of text |
| second line of text |
| third line of text |
| fourth line of text | fifth line of text


Meaning each time
is_response
column is
true
accumulate the text column as the
in_text
and add the new row as
out_text
.

The order of rows is defined by
id
.

Is it possible using pure SQL? How?

Answer

Use the aggregate function string_agg() as window function in a subquery:

SELECT record_id, in_text, out_text  
FROM   (
   SELECT record_id, text AS out_text, is_response
        , string_agg(text, E'\n') OVER (PARTITION BY record_id ORDER BY id
                                        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS in_text
   FROM   tbl
   ) sub
WHERE  is_response;

The special feature here is to add adjust the frame definition with a ROWS clause. Related:

SQL Fiddle. (Line breaks are converted to spaces in sqlfiddle.)