SQLite Forum

How to cancatenate all lines in table to a single string?
Login

How to cancatenate all lines in table to a single string?

(1) By anonymous on 2021-01-02 16:56:48 [link]

Hello,

Let's say we have table with variable number of rows containing text -

    sqlite> create table tab1(line);
    sqlite> insert into tab1 select 'text 1';
    sqlite> insert into tab1 select 'text 2';
    sqlite> insert into tab1 select 'text 3';
    ...

How can I select the following result `text 1 text 2 text 3 ...`?

-Shane

(2) By anonymous on 2021-01-02 17:30:35 in reply to 1 [link]

Perhaps [group_concat aggregate function](https://www.sqlite.org/lang_aggfunc.html#groupconcat) is just the thing.

(3) By David Raymond (dvdraymond) on 2021-01-04 14:36:22 in reply to 2 [link]

Just a note that I do not believe that you can explicitly order group_concat() in SQLite. So if you need it to be in a specific order: 'Line1 Line2 Line3' then you'll have to use something else as you might get 'Line3 Line2 Line1'. If arbitrary ordering is fine, then yes, use group_concat()

(4) By Larry Brasfield (LarryBrasfield) on 2021-01-04 15:10:32 in reply to 3 [link]

Your warning is technically correct. I have found that the ordering of group members to be concatenated can be determined, even while understanding that the means for doing so are outside the set theory basis of the query language. It would be comforting to see an order guarantee for the results of "order by ..." clauses in inner queries.

(5) By Keith Medcalf (kmedcalf) on 2021-01-04 19:14:49 in reply to 3

The ORDER BY clause applies order to the OUTPUT of a projection (SELECT statement), not the INPUT to the projection.

SELECT group_concat(x) from x ORDER BY x;

makes absolutely no sense what so ever because the ORDER BY is applied AFTER the grouping is done to the OUTPUT rows (in this case only 1) and therefore does absolutely nothing.  It is ill-conceived from the get-go as a possible solution and any rational person knows it has absolutely zero possibility of working.

You want to sort the rows GOING INTO the aggregate, not COMING OUT OF the aggregate:

SELECT group_concat(x) from (select x from x order by x);

That has a CHANCE of working.  By happenstance it also does work.