CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.4
Dmitry Volkov
Dmitry Volkov

Reputation: 1337

Uniting data into one row

I have the following data:

materials
==========================
id       | quantity |type
---------+----------+-----
1        |111       |1
2        |240       |2
3        |412       |2
4        |523       |1

For the sake of the simplicity of the example, let's say I need to select materials into pairs by types, so the desirable result would look like that:

id       | quantity |type |id       | quantity |type
---------+----------+-----+---------+----------+-----
1        |111       |1    |2        |240       |2
4        |412       |1    |3        |412       |2

Data will match perfectly, so there would be no empty entries in pairs.
So far I can think only of union, like that:

select * from materials where type = 1
union all
select * from materials where type = 2

But obviously, that's not what I'm looking for. Is that even possible?
P.S. Please, do not simplify the answer to ...where type in (1,2), because actual condition is not mergeable like that.

Upvotes: 0

Views: 57

Answers (2)

Emilio Platzer
Emilio Platzer

Reputation: 2479

You can JOIN separate queries for type 1 and 2. Eeach query with row_number() function

create table materials(
  id integer primary key,
  quantity integer,
  type integer
);

insert into materials values
  (1, 111, 1),
  (2, 240, 2),
  (3, 412, 2),
  (4, 523, 1),
  (5, 555, 2),
  (6, 666, 1);

select * 
  from (
    select *, row_number() over (order by id) as rownum
      from materials
      where type=1
    ) t1 inner join (
    select *, row_number() over (order by id) as rownum
      from materials
      where type=2
    ) t2 on t1.rownum = t2.rownum

You can try it here: http://rextester.com/XMGD76001

Refs:

Upvotes: 1

rd_nielsen
rd_nielsen

Reputation: 2459

Assuming that "conditions will find entities that exactly need to be paired" results in a table called pairs with columns id1 and id2 then

select
    p.id1,
    m1.quantity,
    m1.type,
    p.id2,
    m2.quantity,
    m2.type
from
    pairs p
    inner join materials as m1 on m1.id=p.id1
    inner join materials as m2 on m2.id=p.id2;

Upvotes: 1

Related Questions