Skip site navigation (1) Skip section navigation (2)

Concatenating several rows with a semicolon

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Concatenating several rows with a semicolon
Date: 2010-12-28 15:33:13
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general

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:

                                            u.login > u.logout as online
                                     from pref_users u, pref_money m where

m.yw=to_char(current_timestamp, 'YYYY-IW') and
                                     order by 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


pgsql-general by date

Next:From: Scott MarloweDate: 2010-12-28 15:42:46
Subject: Re: DATA Location
Previous:From: Andreas KretschmerDate: 2010-12-28 15:18:48
Subject: Re: DATA Location

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group