Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 222281337BC4 for ; Fri, 6 May 2011 02:41:45 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 05193-01 for ; Fri, 6 May 2011 05:41:37 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from nm10-vm0.bullet.mail.bf1.yahoo.com (nm10-vm0.bullet.mail.bf1.yahoo.com [98.139.213.147]) by mail.postgresql.org (Postfix) with SMTP id 31ABB1337BB4 for ; Fri, 6 May 2011 02:41:36 -0300 (ADT) Received: from [98.139.212.144] by nm10.bullet.mail.bf1.yahoo.com with NNFMP; 06 May 2011 05:41:36 -0000 Received: from [98.139.212.221] by tm1.bullet.mail.bf1.yahoo.com with NNFMP; 06 May 2011 05:41:36 -0000 Received: from [127.0.0.1] by omp1030.mail.bf1.yahoo.com with NNFMP; 06 May 2011 05:41:36 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 384583.88453.bm@omp1030.mail.bf1.yahoo.com Received: (qmail 78672 invoked by uid 60001); 6 May 2011 05:41:36 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1304660496; bh=XGaaZERBR/dtS/R9GS3ajXGtowwlfheMHohYLfoPGGo=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=v+x0oLdm342zwT5S7gPPwmxW1/CbIiV4YOZyMOZbrPep0GYQPuUyQiGOBjNXvK5uuKM4w8Gns8q8f2RuyYyRY5PTiSIaeFyHANNtFAcL8kPVzQJrB9iz2J2PDya4IJZBpentu2yD4nde/UCMVeX455Yy98I+utB1v0RPrl5t1YU= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=4BqutWFR/8AhRDfT4sw4ZVUztcIYyXAjQiTGYSgwpRK1xKTwugw+Hnumsk++FNgFGKCVMjy17dUVtd3gW08L/FczUKafxHI5OkPsnzcOg9HfSr/ZiniWC1zZrx3qAz7yviKSPxlu7MkiXIITFYeIW/vxYfR4FlqsW93WDN2JS1k=; Message-ID: <196763.62486.qm@web161316.mail.bf1.yahoo.com> X-YMail-OSG: vDr_004VM1mxffhVS.EZJ52TQ0TnnOxaZg0_4WYb07UwbcS pezeGWj3mtcQmTlkXxgoMHc6p.BL9jdNgo5Ox_6HmKJbVRtdO1J8SejQQZGt R.oMSmCiVxFKFDdgdk5q_D6nyD7w9wMwSrSF6V1ssjeR8WvxaKfBwj9vAXP. DRUeWGI1ar.m8Sc.ICcPT7foRIiwLdcHMG2G.qQh09gnvR.SoC0A9wkVQSKl G7RnAnDkwMZ1uAGbth9knzjYlFWU91IyKFxOpsFUdjWYOdYO7VmgiLmkAL1X 3EPfT6rS5Mw5H0x28ZeTsBoxbzeiZF2SBUfqCfZM70WIA6B_K1mY7GKZ1ALA jszx0VMIq4IQMzCLaIk1XtdHsIzXqjFTy.ZbPjZc1QmbsKIlJgAstQN0rHnM ShCj6Wuhtb2MUBCuoaOfscZ77nbacn41749fujtbRaxVl_cD9TgDAoOYiB9K PkG16OGgfioXymRCX67WwG7A.zFR6qZxL0loQwHGGRUVEURsL1FrxgvT3lQ- - Received: from [135.11.3.4] by web161316.mail.bf1.yahoo.com via HTTP; Thu, 05 May 2011 22:41:36 PDT X-Mailer: YahooMailClassic/12.0.2 YahooMailWebService/0.8.110.299900 Date: Thu, 5 May 2011 22:41:36 -0700 (PDT) From: Claudio Adriano Guarracino Subject: Re: Select and merge rows? To: pgsql-sql@postgresql.org, "scorpdaddy@hotmail.com" In-Reply-To: <597398.41914.qm@web161309.mail.bf1.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-721842803-1304660496=:62486" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.921 tagged_above=-5 required=5 tests=BAYES_20=-0.001, FREEMAIL_FROM=0.001, FREEMAIL_REPLY=1.929, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, RFC_ABUSE_POST=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: * X-Archive-Number: 201105/34 X-Sequence-Number: 35318 --0-721842803-1304660496=:62486 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Hi again: I can did the same with crosstab: SELECT * FROM crosstab ( 'select id, order, value from test ORDER BY 1', 'select distinct order from test ORDER BY 1' ) AS ( =A0=A0=A0 id numeric(20), =A0=A0=A0 value1 text, =A0=A0=A0 value2 text, =A0=A0=A0 value3 text ); http://www.postgresql.org/docs/current/interactive/tablefunc.html =0AIn this case, i use: F.36.1.4. - crosstab(text, text). Thanks to Osvaldo Kussama for this help! --- On Thu, 5/5/11, Claudio Adriano Guarracino wrote: From: Claudio Adriano Guarracino Subject: Re: [SQL] Select and merge rows? To: pgsql-sql@postgresql.org, "scorpdaddy@hotmail.com" Date: Thursday, May 5, 2011, 9:06 PM Thank you very much! Your example help me a lot! The original query is more complex, but I can continue with this example. Thanks again! --- On Thu, 5/5/11, scorpdaddy@hotmail.com wrote: From: scorpdaddy@hotmail.com Subject: Re: [SQL] Select and merge rows? To: "Claudio Adriano Guarracino" , pgsql-sql@postgresql.= org Date: Thursday, May 5, 2011, 5:41 PM While there is insufficient information provided (a couple of table snippet= s), you may consider and experiment with the snippet below to get you start= ed. SELECT=20 =A0ids.id, =A0f1.value AS value1, =A0f2.value AS value2, =0A =A0f3.value AS value3 FROM ( SELECT DISTINCT id FROM foo ) AS ids=20 LEFT JOIN foo f1=20 ON f1.id =3D ids.id=20 AND f1.order =3D 1 LEFT JOIN foo f2 ON f2.id =3D ids.id=20 AND f2.order =3D 2=20 LEFT JOIN foo f3=20 ON f3.id =3D ids.id=20 AND f3.order =3D 3 ORDER BY ids.id;=20 ----- Reply message ----- From: "Claudio Adriano Guarracino" Date: Thu, May 5, 2011 5:18 pm Subject: [SQL] Select and merge rows? To: Hello! I have a doubt about a query that I tried to do, but I cant...... This is the scenario: I have a table, with this rows: order=A0=A0=A0 ID=A0=A0=A0 value=A0=A0=A0=20 ---------------------- 1=A0=A0=A0 1000=A0=A0=A0 3 2=A0=A0=A0 1000=A0=A0=A0 5 3=A0=A0=A0 1000=A0=A0=A0 6 1=A0=A0=A0 1001=A0=A0=A0 1 2=A0=A0=A0=0A 1001=A0=A0=A0 2 1=A0=A0=A0 1002=A0=A0=A0 4 2=A0=A0=A0 1002=A0=A0=A0 4 I need to get this table, divided by ID, like this: id=A0=A0=A0 value1=A0=A0=A0 value2=A0=A0=A0 value3 -------------------------------- 1000=A0=A0=A0 3=A0=A0=A0 5=A0=A0=A0 6 1001=A0=A0=A0 1=A0=A0=A0 2 1002=A0=A0=A0 1=A0=A0=A0 2 How I can do this? I tried with cursors and view, but i can't.... Any help is welcome! Thanks in advance! Regards, --0-721842803-1304660496=:62486 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
Hi again:
I can did the same with crosstab= :

SELECT * FROM crosstab
(
'select id, order, value from test = ORDER BY 1',
'select distinct order from test ORDER BY 1'
)
AS
= (
    id numeric(20),
    value1 text,<= br>    value2 text,
    value3 text
);<= br>
http://www.postgresql.org/docs/current/interactive/tablefunc.html=0AIn this case, i use: F.36.1.4. - crosstab(text, text).

Thanks to= Osvaldo Kussama for this help!

--- On Thu, 5/5/11, Claudio Adria= no Guarracino <elninon@yahoo.com> wrote:

From: Claudio Adriano Guarracino <elninon@yahoo.com>Subject: Re: [SQL] Select and merge rows?
To: pgsql-sql@postgresql.org= , "scorpdaddy@hotmail.com" <scorpdaddy@hotmail.com>
Date: Thursday= , May 5, 2011, 9:06 PM

Thank you very much!
Your example help me a lot!
The= original query is more complex, but I can continue with this example.
T= hanks again!

--- On Thu, 5/5/11, scorpdaddy@hotmail.com <sc= orpdaddy@hotmail.com> wrote:

From: scorpdad= dy@hotmail.com <scorpdaddy@hotmail.com>
Subject: Re: [SQL] Select = and merge rows?
To: "Claudio Adriano Guarracino" <elninon@yahoo.com&g= t;, pgsql-sql@postgresql.org
Date: Thursday, May 5, 2011, 5:41 PM
While there is insufficient information provided= (a couple of table snippets), you may consider and experiment with the sni= ppet below to get you started.


SELECT
 ids.id,
&nb= sp;f1.value AS value1,
 f2.value AS value2,
=0A  f3.value = AS value3
FROM
( SELECT DISTINCT id FROM foo ) AS ids
LEFT JOIN f= oo f1
ON f1.id =3D ids.id
AND f1.order =3D 1
LEFT JOIN foo f2ON f2.id =3D ids.id
AND f2.order =3D 2
LEFT JOIN foo f3
ON f3.= id =3D ids.id
AND f3.order =3D 3
ORDER BY ids.id;


----- = Reply message -----
From: "Claudio Adriano Guarracino" <elninon@yahoo= .com>
Date: Thu, May 5, 2011 5:18 pm
Subject: [SQL] Select and mer= ge rows?
To: <pgsql-sql@postgresql.org>

Hello!
I have a = doubt about a query that I tried to do, but I cant......
This is the sce= nario:
I have a table, with this rows:
order    ID&nbs= p;   value   
----------------------
1 = ;   1000    3
2    1000 &nb= sp;  5
3    1000    6
1  = ;  1001    1
2   =0A 1001  =   2
1    1002    4
2  &n= bsp; 1002    4

I need to get this table, divided by I= D, like this:
id    value1    value2 =    value3
--------------------------------
1000  =   3    5    6
1001    = 1    2
1002    1    2
How I can do this?
I tried with cursors and view, but i can't....
A= ny help is welcome!

Thanks in advance!
Regards,



--0-721842803-1304660496=:62486--