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 | |
by chacham (Prior) on Dec 14, 2015 at 16:32 UTC | |
by frasco (Beadle) on Dec 14, 2015 at 16:55 UTC | |
by chacham (Prior) on Dec 15, 2015 at 15:58 UTC | |
by choroba (Cardinal) on Dec 14, 2015 at 18:16 UTC |