Re: Concatenating several rows with a semicolon - Mailing list pgsql-general

> -----Original Message-----
> From: Alexander Farber [mailto:alexander.farber@gmail.com]
> Sent: Tuesday, December 28, 2010 10:33 AM
> To: pgsql-general@postgresql.org
> Subject: Concatenating several rows with a semicolon
>
> Hello,
>
> I'm working on a small app, which receives a list of 20
> players in XML format.
>
> The initial version works ok and I use there just 1 SQL
> statement and thus it is easy for me to fetch results row by
> row and print XML at the same time:
>
>                                             select u.id,
>                                             u.first_name,
>                                             u.city,
>                                             u.avatar,
>                                             m.money,
>                                             u.login >
> u.logout as online
>                                      from pref_users u,
> pref_money m where
>
> m.yw=to_char(current_timestamp, 'YYYY-IW') and
>                                             u.id=m.id
>                                      order by m.money desc
>                                      limit 20 offset ?
>
> My problem is however, that I need to add more data for each
> user representing their statistics over the last 20 weeks.
> And that data is in separate tables: pref_money, pref_pass, pref_game:
>
> # select yw, money
> from pref_money where id='OK122471020773'
> order by yw desc limit 20;
>    yw    | money
> ---------+-------
>  2010-52 |   760
>  2010-51 |  3848
>  2010-50 |  4238
>  2010-49 |  2494
>  2010-48 |   936
>  2010-47 |  3453
>  2010-46 |  3923
>  2010-45 |  1110
>  2010-44 |   185
> (9 rows)
>
> For example for the table above I'd like to concatenate those
> rows and add them as an XML attribute for that user:
>
> <user id="OK122471020773" first_name="..." city="..." ...
>     pref_money="2010-52:760;2010-51:3848;2010-50:4238;...." />
>
> so that I can take that attribute in my app and use it in a chart.
>
> My problem is that I don't know how to bring this together in
> 1 SQL statement (i.e. the SQL statement at the top and then
> the concatenated 20 rows from 3 tables).
>
> Is it possible? Maybe I need to write a PgPlSQL procedure for
> each of the 3 tables and then add them to the SQL statement
> above? But how do I concatenate the rows, should I create a
> PgPlSQL variable and always append values to it in a loop or
> is there a better way?
>
> Thank you for any hints
> Alex
>

Based on your PG version there are different solutions to your problem.
Not to re-invent the wheel, check this article:
http://www.postgresonline.com/journal/archives/191-String-Aggregation-in
-PostgreSQL%2C-SQL-Server%2C-and-MySQL.html%23extended

Regards,
Igor Neyman