2012/11/27 ssylla <stefansy...@gmx.de>:
> assuming I have the following n:n relationship:
>
> intermediary table:
> t3
> id_project|id_product
> 1|1
> 1|2
> 2|1
>
> How can I create an output like this:
> id_project|id_product1|id_product2
> 1|1|2
> 2|1|NULL

I'd said the sample is too simplified — not clear which id_product
should be picked if there're more then 2 exists.
I assumed the ones with smallest IDs.

-- this is just a sample source generator
WITH t3(id_project, id_product) AS (VALUES (1,1),(1,2),(2,1))
-- this is the query
SELECT l.id_project, min(l.id_product) id_product1, min(r.id_product)
id_product2
  FROM t3 l
  LEFT JOIN t3 r ON l.id_project=r.id_project AND l.id_product < r.id_product
 GROUP BY l.id_project;


-- 
Victor Y. Yegorov


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Reply via email to