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

In response to "Igor Neyman" <ineyman@perceptron.com>:
>
>
> > -----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

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/