Make a query making groups on the same result row

Question

I have two tables. Like this.

select * from extrafieldvalues;
+----------------------------+
| id | value | type | idItem |
+----------------------------+
| 1  | 100   | 1    | 10     |
| 2  | 150   | 2    | 10     |
| 3  | 101   | 1    | 11     |
| 4  | 90    | 2    | 11     |
+----------------------------+

select * from items
+------------+
| id  | name |
+------------+
| 10  | foo  |
| 11  | bar  |
+------------+

I need to make a query and get something like this:

+--------------------------------------+
| idItem  | valtype1 | valtype2 | name |
+--------------------------------------+
| 10      | 100      | 150      | foo  |
| 11      | 101      | 90       | bar  |
+--------------------------------------+

The quantity of types of extra field values is variable, but every item ALWAYS uses every extra field.

It's called a pivot query. StackOverflow has many examples of thi, one is https://stackoverflow.com/questions/1237068/pivot-in-sqlite - see haridev's answer — Caius Jard, Feb 07 '20 at 06:55
As I told, the "extra fields" are variable, can be 2, 3, ... , n — sudo_woodo, Feb 07 '20 at 07:03
You'll need to generate the query programmatically in your front end then, after doing a select distinct for all the relevant values — Caius Jard, Feb 07 '20 at 07:32

score 2 · Answer 1 · answered Feb 07 '20 at 06:55

2

Use conditional aggregation

select iditem, 
       max(case when type=1 then value end) as valtype1,
       max(case when type=2 then value end) as valtype2,name
from extrafieldvalues a inner join items b on a.iditem=b.id
group by iditem,name

answered Feb 07 '20 at 06:55

Fahmi

37,315
5
22
31

This is great. Why did you use max with it? Also, without max it does not work. Why it is like this? – Arch Desai Feb 07 '20 at 15:30
@ArchDesai, you can use min/sum also, the point is you need to use aggregate function , btw, if it helped u please consider it to mark as accepted – Fahmi Feb 07 '20 at 16:02

score 2 · Accepted Answer · answered Feb 07 '20 at 12:56

2

If you have only two fields, then left join is an option for this:

select i.*, efv1.value as value_1, efv2.value as value_2
from items i left join
     extrafieldvalues efv1
     on efv1.iditem = i.id and
        efv1.type = 1 left join
     extrafieldvalues efv2
     on efv1.iditem = i.id and
        efv1.type = 2 ;

In terms of performance, two joins are probably faster than an aggregation -- and it makes it easier to bring in more columns from items. One the other hand, conditional aggregation generalizes more easily and the performance changes by little as more columns from extrafieldvalues are added to the select.

answered Feb 07 '20 at 12:56

Gordon Linoff

1,242,037
58
646
786

I'm making this (I'm generating the query programatically) `select itemes.*, valor0.datoValor as Voltaje, valor1.datoValor as Foto, valor2.datoValor as Volumen, valor3.datoValor as Marca, valor4.datoValor as Color from itemes left join valores valor0 on valor0.idItem=itemes.idItem left join valores valor1 on valor1.idItem=itemes.idItem left join valores valor2 on valor2.idItem=itemes.idItem left join valores valor3 on valor3.idItem=itemes.idItem left join valores valor4 on valor4.idItem=itemes.idItem` But I'm getting results of more than 60M of rows (and ITEMES table has 440) – sudo_woodo Feb 13 '20 at 07:12

Make a query making groups on the same result row

2 Answers2