pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAppend n Rows into a Single Row
5+ messages / 4 participants
[nested] [flat]
* Append n Rows into a Single Row
@ 2011-06-18 00:20 Tripura <k_tripura@hotmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Tripura @ 2011-06-18 00:20 UTC (permalink / raw)
To: pgsql-sql
Hi,
I am totally new to PostgreSQL and this is my first script.
Can anyone please help me with my following requirement
I have script that returns 1 column and 40 rows.
My requirement is update this existing script to show all the 40 rows data
in a single row as a string.
For example
The current script returns, something like this
Tom
Tad
Max
Ted
I have to update the existing script to show the above results as follow
Tom || Tad || Max || Ted
Something like colace in Ms sql
Any help is really appriciated.
Thankyou in Advance.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Append-n-Rows-into-a-Single-Row-tp4500306p4500306.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Append n Rows into a Single Row
@ 2011-06-18 22:05 Samuel Gendler <sgendler@ideasculptor.com>
parent: Tripura <k_tripura@hotmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Samuel Gendler @ 2011-06-18 22:05 UTC (permalink / raw)
To: Tripura <k_tripura@hotmail.com>; +Cc: pgsql-sql
Look at the documentation for the tablefunc contrib module. It provides a crosstab() function which will allow you to pivot multiple rows into multiple columns in a single row, with all rows that share a particular key in one or more columns pivoting up into the same row, but different keys ending up as separate rows. You could then string concatenate all columns of a single row into a single string if desired,
Sent from my iPhone
On Jun 17, 2011, at 17:20, Tripura <k_tripura@hotmail.com> wrote:
> Hi,
> I am totally new to PostgreSQL and this is my first script.
> Can anyone please help me with my following requirement
>
> I have script that returns 1 column and 40 rows.
> My requirement is update this existing script to show all the 40 rows data
> in a single row as a string.
>
> For example
> The current script returns, something like this
> Tom
> Tad
> Max
> Ted
>
> I have to update the existing script to show the above results as follow
> Tom || Tad || Max || Ted
>
> Something like colace in Ms sql
>
> Any help is really appriciated.
>
> Thankyou in Advance.
>
>
>
>
>
>
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/Append-n-Rows-into-a-Single-Row-tp4500306p4500306.html
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Append n Rows into a Single Row
@ 2011-06-19 03:33 Rob Sargent <robjsargent@gmail.com>
parent: Tripura <k_tripura@hotmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Rob Sargent @ 2011-06-19 03:33 UTC (permalink / raw)
To: Tripura <k_tripura@hotmail.com>; +Cc: pgsql-sql
Look at the array aggregation functions here
<http://www.postgresql.org/docs/9.0/static/functions-array.html;
Tripura wrote:
> Hi,
> I am totally new to PostgreSQL and this is my first script.
> Can anyone please help me with my following requirement
>
> I have script that returns 1 column and 40 rows.
> My requirement is update this existing script to show all the 40 rows data
> in a single row as a string.
>
> For example
> The current script returns, something like this
> Tom
> Tad
> Max
> Ted
>
> I have to update the existing script to show the above results as follow
> Tom || Tad || Max || Ted
>
> Something like colace in Ms sql
>
> Any help is really appriciated.
>
> Thankyou in Advance.
>
>
>
>
>
>
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/Append-n-Rows-into-a-Single-Row-tp4500306p4500306.html
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Append n Rows into a Single Row
@ 2011-06-19 03:51 Samuel Gendler <sgendler@ideasculptor.com>
parent: Rob Sargent <robjsargent@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Samuel Gendler @ 2011-06-19 03:51 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; +Cc: Tripura <k_tripura@hotmail.com>; pgsql-sql
Actually, you need the array_agg() function to aggregate multiple rows into
a single array, and that is discussed on the aggregate functions page,
here: http://www.postgresql.org/docs/9.0/static/functions-aggregate.html
You could then use one of the other array functions to manipulate that
array.
But given that you are appending strings to each other in your example, you
can also just use the string_agg function from that same page - assuming you
are on 9.0. It looks like that function isn't available before 9.0
--sam
On Sat, Jun 18, 2011 at 8:33 PM, Rob Sargent <robjsargent@gmail.com> wrote:
> Look at the array aggregation functions here <http://www.postgresql.org/**
> docs/9.0/static/functions-**array.html<http://www.postgresql.org/docs/9.0/static/functions-array.html;
> >
>
>
> Tripura wrote:
>
>> Hi,
>> I am totally new to PostgreSQL and this is my first script.
>> Can anyone please help me with my following requirement
>> I have script that returns 1 column and 40 rows.
>> My requirement is update this existing script to show all the 40 rows data
>> in a single row as a string.
>>
>> For example The current script returns, something like this Tom
>> Tad
>> Max
>> Ted
>>
>> I have to update the existing script to show the above results as follow
>> Tom || Tad || Max || Ted
>> Something like colace in Ms sql
>>
>> Any help is really appriciated.
>>
>> Thankyou in Advance.
>>
>>
>>
>>
>>
>>
>>
>> --
>> View this message in context: http://postgresql.1045698.n5.**
>> nabble.com/Append-n-Rows-into-**a-Single-Row-**tp4500306p4500306.html<http://postgresql.1045698.n5.nabble.com/Append-n-Rows-into-a-Single-Row-tp4500306p4500306.html;
>> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>>
>>
>>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-sql<http://www.postgresql.org/mailpref/pgsql-sql;
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Append n Rows into a Single Row
@ 2011-06-20 17:05 Ross J. Reedstrom <reedstrm@rice.edu>
parent: Samuel Gendler <sgendler@ideasculptor.com>
0 siblings, 0 replies; 5+ messages in thread
From: Ross J. Reedstrom @ 2011-06-20 17:05 UTC (permalink / raw)
To: Samuel Gendler <sgendler@ideasculptor.com>; +Cc: Rob Sargent <robjsargent@gmail.com>; Tripura <k_tripura@hotmail.com>; pgsql-sql
On Sat, Jun 18, 2011 at 08:51:55PM -0700, Samuel Gendler wrote:
> Actually, you need the array_agg() function to aggregate multiple rows into
> a single array, and that is discussed on the aggregate functions page,
> here: http://www.postgresql.org/docs/9.0/static/functions-aggregate.html
>
> You could then use one of the other array functions to manipulate that
> array.
>
> But given that you are appending strings to each other in your example, you
> can also just use the string_agg function from that same page - assuming you
> are on 9.0. It looks like that function isn't available before 9.0
>
Nice blog post about this here:
http://www.postgresonline.com/journal/archives/191-stringagg.html
Ross
--
Ross Reedstrom, Ph.D. reedstrm@rice.edu
Systems Engineer & Admin, Research Scientist phone: 713-348-6166
Connexions http://cnx.org fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-06-20 17:05 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-06-18 00:20 Append n Rows into a Single Row Tripura <k_tripura@hotmail.com>
2011-06-18 22:05 ` Samuel Gendler <sgendler@ideasculptor.com>
2011-06-19 03:33 ` Rob Sargent <robjsargent@gmail.com>
2011-06-19 03:51 ` Samuel Gendler <sgendler@ideasculptor.com>
2011-06-20 17:05 ` Ross J. Reedstrom <reedstrm@rice.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox