Uniting data into one row

Question

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.

What do you mean by "into pairs by types"? That seems to suggest that you would want to join the two rows with `type` =1, and join the two rows with `type`=2, but your output shows that you have joined items of different type. Why did those particular rows get joined? — rd_nielsen, Jun 25 '17 at 17:56
@LucasKrupinski no, I guess not. That data would go straight to the clientside, and dapper will take care of that. — Dmitry Volkov, Jun 25 '17 at 17:58
@rd_nielsen I meant that I need to return pairs in which one entry is of type=1 and the other is of type=2 — Dmitry Volkov, Jun 25 '17 at 17:59
@rd_nielsen Well, that's not relevant, strictly speaking. In actual code conditions will find enties that exactly need to be paired. So far, I need help with finding a way to combine the 2 rows in one. — Dmitry Volkov, Jun 25 '17 at 18:03

Emilio Platzer · Answer 1 · 2017-06-25T18:26:14.023

1

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:

row_number(): https://www.postgresql.org/docs/9.4/static/functions-window.html
inner join: https://www.postgresql.org/docs/9.4/static/queries-table-expressions.html

edited Jun 25 '17 at 18:26

answered Jun 25 '17 at 18:06

Emilio Platzer

2,327
21
29

I think that it should be `full join` – Abelisto Jun 25 '17 at 18:12
May be `full join`. But if you are sure that the two subqueries matchs `inner join` is most performant. If you not you must handle the `nulls` when you fetch the data – Emilio Platzer Jun 25 '17 at 18:14
doesn't seem to work as expected. btw, I don't need that your solution would match my "desirable result" exactly, so the trick with row_number is unnececcary. I just need the way to pair results of two subqueries. – Dmitry Volkov Jun 25 '17 at 18:14
Sorry. I make a typo. I write type=1 twice. The seconds was type=2. Try it now in your postgresql (dbfidle does not accepts columns with the same name) – Emilio Platzer Jun 25 '17 at 18:20
If you look at http://rextester.com/XMGD76001 and delete rows with id 5 and 6 you get exact your example. I add 5 and 6 to be sure that works with more data. – Emilio Platzer Jun 25 '17 at 18:30
I belive that `row_number` is needed to say "the first with the first, the second with the second..." – Emilio Platzer Jun 25 '17 at 18:33

score 1 · Accepted Answer · answered Jun 25 '17 at 18:14

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;

Uniting data into one row

2 Answers2