Reduced results in accumulated groups

Question

Having the following table (describing the 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 am 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

The value each time a column is_response trueaccumulates a text column as in_textand adds a new row as out_text.

The order of the lines is determined id.

Is it possible to use pure SQL? How?

Answer 1

Use the aggregate function string_agg()as a 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;

A special feature here is the setting of the window frame using the offer ROWS. Connected:

• Grouping by Sequence of Rows

SQL Fiddle. ( sqlfiddle.)