Re: Concatenating several rows with a semicolon

In response to "Igor Neyman" <ineyman(at)perceptron(dot)com>:
>
>
> > -----Original Message-----
> > From: Alexander Farber [mailto:alexander(dot)farber(at)gmail(dot)com]
> > Sent: Tuesday, December 28, 2010 10:33 AM
> > To: pgsql-general(at)postgresql(dot)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/