Thread: Re: [SQL] Select and merge rows?
While there is insufficient information provided (a couple of table snippets), you may consider and experiment with the snippet below to get you started.
SELECT
ids.id,
f1.value AS value1,
f2.value AS value2,
f3.value AS value3
FROM
( SELECT DISTINCT id FROM foo ) AS ids
LEFT JOIN foo f1
ON f1.id = ids.id
AND f1.order = 1
LEFT JOIN foo f2
ON f2.id = ids.id
AND f2.order = 2
LEFT JOIN foo f3
ON f3.id = ids.id
AND f3.order = 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 merge 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 scenario:
I have a table, with this rows:
order ID value
----------------------
1 1000 3
2 1000 5
3 1000 6
1 1001 1
2 1001 2
1 1002 4
2 1002 4
I need to get this table, divided by ID, 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....
Any help is welcome!
Thanks in advance!
Regards,
SELECT
ids.id,
f1.value AS value1,
f2.value AS value2,
f3.value AS value3
FROM
( SELECT DISTINCT id FROM foo ) AS ids
LEFT JOIN foo f1
ON f1.id = ids.id
AND f1.order = 1
LEFT JOIN foo f2
ON f2.id = ids.id
AND f2.order = 2
LEFT JOIN foo f3
ON f3.id = ids.id
AND f3.order = 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 merge 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 scenario:
I have a table, with this rows:
order ID value
----------------------
1 1000 3
2 1000 5
3 1000 6
1 1001 1
2 1001 2
1 1002 4
2 1002 4
I need to get this table, divided by ID, 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....
Any help is welcome!
Thanks in advance!
Regards,
<table border="0" cellpadding="0" cellspacing="0"><tr><td style="font: inherit;" valign="top">Thank you very much!<br />Yourexample help me a lot!<br />The original query is more complex, but I can continue with this example.<br />Thanks again!<br/><br />--- On <b>Thu, 5/5/11, scorpdaddy@hotmail.com <i><scorpdaddy@hotmail.com></i></b> wrote:<br /><blockquotestyle="border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"><br />From: scorpdaddy@hotmail.com<scorpdaddy@hotmail.com><br />Subject: Re: [SQL] Select and merge rows?<br />To: "Claudio AdrianoGuarracino" <elninon@yahoo.com>, pgsql-sql@postgresql.org<br />Date: Thursday, May 5, 2011, 5:41 PM<br /><br/><div id="yiv921114391">While there is insufficient information provided (a couple of table snippets), you may considerand experiment with the snippet below to get you started.<br /><br /><br />SELECT <br /> ids.id,<br /> f1.valueAS value1,<br /> f2.value AS value2,<br /> f3.value AS value3<br />FROM<br />( SELECT DISTINCT id FROM foo ) ASids <br />LEFT JOIN foo f1 <br />ON f1.id = ids.id <br />AND f1.order = 1<br />LEFT JOIN foo f2<br />ON f2.id = ids.id<br />AND f2.order = 2 <br />LEFT JOIN foo f3 <br />ON f3.id = ids.id <br />AND f3.order = 3<br />ORDER BY ids.id;<br /><br /><br />----- Reply message -----<br />From: "Claudio Adriano Guarracino" <elninon@yahoo.com><br />Date:Thu, May 5, 2011 5:18 pm<br />Subject: [SQL] Select and merge rows?<br />To: <pgsql-sql@postgresql.org><br /><br/>Hello!<br />I have a doubt about a query that I tried to do, but I cant......<br />This is the scenario:<br />I havea table, with this rows:<br />order ID value <br />----------------------<br />1 1000 3<br />2 1000 5<br />3 1000 6<br />1 1001 1<br />2 1001 2<br />1 1002 4<br />2 1002 4<br /><br />Ineed to get this table, divided by ID, like this:<br />id value1 value2 value3<br />--------------------------------<br/>1000 3 5 6<br />1001 1 2<br />1002 1 2<br /><br />How I can dothis?<br />I tried with cursors and view, but i can't....<br />Any help is welcome!<br /><br />Thanks in advance!<br />Regards,<br/><br /><br /><br /><br /></div></blockquote></td></tr></table>
<table border="0" cellpadding="0" cellspacing="0"><tr><td style="font: inherit;" valign="top">Hi again:<br />I can did thesame with crosstab:<br /><br />SELECT * FROM crosstab<br />(<br />'select id, order, value from test ORDER BY 1',<br />'selectdistinct order from test ORDER BY 1'<br />)<br />AS<br />(<br /> id numeric(20),<br /> value1 text,<br /> value2 text,<br /> value3 text<br />);<br /><br />http://www.postgresql.org/docs/current/interactive/tablefunc.html<br/> In this case, i use: F.36.1.4. - crosstab(text,text).<br /><br />Thanks to Osvaldo Kussama for this help!<br /><br />--- On <b>Thu, 5/5/11, Claudio AdrianoGuarracino <i><elninon@yahoo.com></i></b> wrote:<br /><blockquote style="border-left: 2px solid rgb(16, 16,255); margin-left: 5px; padding-left: 5px;"><br />From: Claudio Adriano Guarracino <elninon@yahoo.com><br />Subject:Re: [SQL] Select and merge rows?<br />To: pgsql-sql@postgresql.org, "scorpdaddy@hotmail.com" <scorpdaddy@hotmail.com><br/>Date: Thursday, May 5, 2011, 9:06 PM<br /><br /><div id="yiv1507541961"><table border="0"cellpadding="0" cellspacing="0"><tbody><tr><td style="font: inherit;" valign="top">Thank you very much!<br />Yourexample help me a lot!<br />The original query is more complex, but I can continue with this example.<br />Thanks again!<br/><br />--- On <b>Thu, 5/5/11, scorpdaddy@hotmail.com <i><scorpdaddy@hotmail.com></i></b> wrote:<br /><blockquotestyle="border-left: 2px solidrgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"><br />From: scorpdaddy@hotmail.com<scorpdaddy@hotmail.com><br />Subject: Re: [SQL] Select and merge rows?<br />To: "Claudio AdrianoGuarracino" <elninon@yahoo.com>, pgsql-sql@postgresql.org<br />Date: Thursday, May 5, 2011, 5:41 PM<br /><br/><div id="yiv1507541961">While there is insufficient information provided (a couple of table snippets), you may considerand experiment with the snippet below to get you started.<br /><br /><br />SELECT <br /> ids.id,<br /> f1.valueAS value1,<br /> f2.value AS value2,<br /> f3.value AS value3<br />FROM<br />( SELECT DISTINCT id FROM foo ) ASids <br />LEFT JOIN foo f1 <br />ON f1.id = ids.id <br />AND f1.order = 1<br />LEFT JOIN foo f2<br />ON f2.id = ids.id<br />AND f2.order = 2 <br />LEFT JOIN foo f3 <br />ON f3.id = ids.id <br />AND f3.order = 3<br />ORDER BY ids.id;<br /><br /><br />----- Reply message -----<br />From: "Claudio Adriano Guarracino" <elninon@yahoo.com><br />Date:Thu, May 5, 2011 5:18 pm<br />Subject: [SQL] Select and merge rows?<br />To: <pgsql-sql@postgresql.org><br /><br/>Hello!<br />I have a doubt about a query that I tried to do, but I cant......<br />This is the scenario:<br />I havea table, with this rows:<br />order ID value <br />----------------------<br />1 1000 3<br />2 1000 5<br />3 1000 6<br />1 1001 1<br />2 1001 2<br />1 1002 4<br />2 1002 4<br /><br />Ineed to get this table, divided by ID, like this:<br />id value1 value2 value3<br />--------------------------------<br/>1000 3 5 6<br />1001 1 2<br />1002 1 2<br /><br />How I can dothis?<br />I tried with cursors and view, but i can't....<br />Any help is welcome!<br /><br />Thanks in advance!<br />Regards,<br/><br /><br /><br /><br /></div></blockquote></td></tr></tbody></table></div></blockquote></td></tr></table>