in reply to Fetching data from DB and complex data structures

without GROUP_CONCT and similar database functions)

Do you mean without a special group concatenation function(), or without any functions at all? This can be easily done with a recursive query.

WITH Builder(line_n, word, word_position, amount) AS ( SELECT line_n, '', 0, MAX(word_position) FROM table GROUP BY line_n UNION ALL SELECT Data.line_n, CONCAT(Builder.word, CONCAT(' ', Data.word)), Data.word_position, Builder.amount FROM Builder, table WHERE table.line_n = table.line_n AND table.word_position = table.word_position + 1 ) SELECT line_n, word FROM Builder WHERE word_position = amount;

The double CONCAT() was used to avoid casting issues; you may be able to remove them. Depending on your RDBMS, you may need to specify the RECURSIVE keyword.

Also, if you don't mind, a side point, please do not use * outside of COUNT(), EXISTS(), and ad hoc queries. Specifying the columns--id, line_n, word, word_position--is easy, self-documenting, and protects against column changes and reordering.

Perhaps it is worth mentioning, the id column is redundant, as line_n and word_position must be unique anyway.

Replies are listed 'Best First'.
Re^2: Fetching data from DB and complex data structures
by frasco (Beadle) on Dec 14, 2015 at 16:26 UTC

    Thank you for you help, but in fact I would like to avoid SQL functions at all. For instance, GROUP_CONCAT or string_agg would be great. I need to manage these data before their way to the HTML template. Moreover, just to clarify my question, neither line_n nor word_position are unique values.

      If line_n and word_position (together) are not unique, how in the world do you build the string? Well, unless id breaks the ties.

      If you want to see the work in progress, the recursive query is even easier. (No MAX in the anchor, and no WHERE clause is the main query.)

        I'm not a software developer and maybe I miss something but the SQL solution I normally work with is:
        SELECT line_n,
        	GROUP_CONCAT(word ORDER BY id SEPARATOR ' ')
        	FROM myTable
        	WHERE myText = 'something'
        	GROUP BY line_n
        ORDER BY id
        
        In fact it is governed by the id