In response to "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>: > > > > -----Original Message----- > > From: Alexander Farber [mailto:alexander.farber@xxxxxxxxx] > > Sent: Tuesday, December 28, 2010 10:33 AM > > To: pgsql-general@xxxxxxxxxxxxxx > > 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 This doesn't invalidate Igor's response, but you're using XML wrong. If there are multiple entries for pref_money, then each one should be a container inside user, i.e.: <user id="bla bla bla ...> <pref_money date="2010-52" money="760" /> <pref_money date="2010-51" money="3848" /> ... etc ... </user> But then again, it appears as if your yw field is a textual field being used to store a date, so I expect you have bigger problems coming down the pike. In all essence, you XML should probably look like this: <user id="bla bla bla ...> <pref_money year="2010" week="52" money="760" /> <pref_money year="2010" week="51" money="3848" /> ... etc ... </user> And that yw field should be replaced with a week_ending field that is a date type. You can extract that into year and week using date_part(). Just 15 years of DB experience making me antsy ... does this make me one of those people who freak out when someone says something wrong on a message board and just _HAS_ to correct them? -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
