pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSelect and merge rows?
6+ messages / 3 participants
[nested] [flat]
* Select and merge rows?
@ 2011-05-05 20:18 Claudio Adriano Guarracino <elninon@yahoo.com>
0 siblings, 1 reply; 6+ messages in thread
From: Claudio Adriano Guarracino @ 2011-05-05 20:18 UTC (permalink / raw)
To: pgsql-sql
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,
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Select and merge rows?
@ 2011-05-05 20:24 Oliveiros <oliveiros.cristina@gmail.com>
parent: Claudio Adriano Guarracino <elninon@yahoo.com>
0 siblings, 0 replies; 6+ messages in thread
From: Oliveiros @ 2011-05-05 20:24 UTC (permalink / raw)
To: Claudio Adriano Guarracino <elninon@yahoo.com>; +Cc: pgsql-sql
Howdy!
Is there a maximum ceilling of three values per order ID? or an ID can have
an arbitrary number of values?
Best,
Oliveiros
2011/5/5 Claudio Adriano Guarracino <elninon@yahoo.com>
> 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,
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Select and merge rows?
@ 2011-05-05 20:53 Claudio Adriano Guarracino <elninon@yahoo.com>
0 siblings, 0 replies; 6+ messages in thread
From: Claudio Adriano Guarracino @ 2011-05-05 20:53 UTC (permalink / raw)
To: pgsql-sql
Excuse me,
The original table is:
order ID value
----------------------
1 1000 3
2 1000 5
3 1000 6
1 1001 1
2 1001 2
1 1002 4
2 1002 4
The result of table should be:
id value1 value2 value3
----------------------------------------------------
1000 3 5 6
1001 1 2
1002 4 4
Thanks!
Regards,
--- On Thu, 5/5/11, Claudio Adriano Guarracino <elninon@yahoo.com> wrote:
From: Claudio Adriano Guarracino <elninon@yahoo.com>
Subject: Select and merge rows?
To: pgsql-sql@postgresql.org
Date: Thursday, May 5, 2011, 4:18 PM
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,
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] Select and merge rows?
@ 2011-05-05 21:41 Charlie <scorpdaddy@hotmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Charlie @ 2011-05-05 21:41 UTC (permalink / raw)
To: Claudio Adriano Guarracino <elninon@yahoo.com>; pgsql-sql
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,
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Select and merge rows?
@ 2011-05-06 01:06 Claudio Adriano Guarracino <elninon@yahoo.com>
parent: Charlie <scorpdaddy@hotmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Claudio Adriano Guarracino @ 2011-05-06 01:06 UTC (permalink / raw)
To: pgsql-sql@postgresql.org, "scorpdaddy@hotmail.com" <scorpdaddy@hotmail.com>
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 <scorpdaddy@hotmail.com> wrote:
From: scorpdaddy@hotmail.com <scorpdaddy@hotmail.com>
Subject: Re: [SQL] Select and merge rows?
To: "Claudio Adriano Guarracino" <elninon@yahoo.com>, 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 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,
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Select and merge rows?
@ 2011-05-06 05:41 Claudio Adriano Guarracino <elninon@yahoo.com>
parent: Claudio Adriano Guarracino <elninon@yahoo.com>
0 siblings, 0 replies; 6+ messages in thread
From: Claudio Adriano Guarracino @ 2011-05-06 05:41 UTC (permalink / raw)
To: pgsql-sql@postgresql.org, "scorpdaddy@hotmail.com" <scorpdaddy@hotmail.com>
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,
value2 text,
value3 text
);
http://www.postgresql.org/docs/current/interactive/tablefunc.html
In 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 <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.
Thanks again!
--- On Thu, 5/5/11, scorpdaddy@hotmail.com <scorpdaddy@hotmail.com> wrote:
From: scorpdaddy@hotmail.com <scorpdaddy@hotmail.com>
Subject: Re: [SQL] Select and merge rows?
To: "Claudio Adriano Guarracino" <elninon@yahoo.com>, 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 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,
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2011-05-06 05:41 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-05-05 20:18 Select and merge rows? Claudio Adriano Guarracino <elninon@yahoo.com>
2011-05-05 20:24 ` Oliveiros <oliveiros.cristina@gmail.com>
2011-05-05 20:53 Select and merge rows? Claudio Adriano Guarracino <elninon@yahoo.com>
2011-05-05 21:41 Re: [SQL] Select and merge rows? Charlie <scorpdaddy@hotmail.com>
2011-05-06 01:06 ` Claudio Adriano Guarracino <elninon@yahoo.com>
2011-05-06 05:41 ` Claudio Adriano Guarracino <elninon@yahoo.com>
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