Reduce results into accumulated groups

Question

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?

But, since `id` is not UNIQUE, there *must* be another key element ... — wildplasser, Aug 31 '16 at 11:47
You seem to assume a natural order of rows, which does not exist in relational DB tables. You need some way to order rows, Postgres does not know that "first line of text" should come first. — Erwin Brandstetter, Aug 31 '16 at 11:49
Does it matter for the solution? if so I'll change id to record_id and add another column — Shlomi Schwartz, Aug 31 '16 at 11:49
Yes: it matters (not only for the solution) A table without a candidate key is basically useless. — wildplasser, Aug 31 '16 at 11:56
I have this weird feeling, that you try to solve an entirely different problem and assume this would be the way. This could be absolutely true of course, but I'm reluctant to wrap my hand around this particular question. Why would you want such a thing? — Jakumi, Aug 31 '16 at 12:03
I took the liberty to adjust the desired result to your changed setup. — Erwin Brandstetter, Aug 31 '16 at 12:07

score 1 · Accepted Answer · edited May 23 '17 at 10:29

1

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 adjust the window frame with a ROWS clause. Related:

Grouping based on sequence of rows

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

edited May 23 '17 at 10:29

Community

1
1

answered Aug 31 '16 at 12:01

Erwin Brandstetter

605,456
145
1,078
1,228

Works like a charm :) – Shlomi Schwartz Aug 31 '16 at 12:22
could you please have a look at http://stackoverflow.com/questions/39577026/postgres-select-partitions-based-on-matches/39583041#39583041 the answer is almost complete, but fails to aggregate single reponses, tahnks :) – Shlomi Schwartz Sep 20 '16 at 11:33

Reduce results into accumulated groups

1 Answers1