Re: Concatenating several rows with a semicolon

2010/12/28 Dmitriy Igrishin <dmitigr(at)gmail(dot)com>

>
>
> 2010/12/28 Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
>
> 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)
>>
> SELECT string_agg(yw::text || money::text, ';');
>
Sorry,
SELECT string_agg(yw::text || ':' || money::text, ';');

>
>> 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
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
>
> --
> // Dmitriy.
>
>
>

--
// Dmitriy.