Firebird 3.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE articulos (
  ProductId INTEGER PRIMARY KEY,
  LongSKU varchar(12) NOT NULL
  );
INSERT INTO articulos VALUES (1, 'A22121000125');
1 rows affected

INSERT INTO articulos VALUES (2, 'A22121000138');
1 rows affected

INSERT INTO articulos VALUES (3, 'A22123001508');
1 rows affected

INSERT INTO articulos VALUES (4, 'A22124002001');
1 rows affected

CREATE TABLE TALLESPORARTICULOS (
  ProductId INTEGER NOT NULL,
  ProdPosition INTEGER NOT NULL,
  Sizes varchar(12) NOT NULL
  );
INSERT INTO TALLESPORARTICULOS (ProductId, Prodposition, Sizes) VALUES(1, 1, 'SMALL')
1 rows affected

INSERT INTO TALLESPORARTICULOS (ProductId, Prodposition, Sizes) VALUES(1, 2, 'MEDIUM')
1 rows affected

INSERT INTO TALLESPORARTICULOS (ProductId, Prodposition, Sizes) VALUES(1, 3, 'LARGE')
1 rows affected

INSERT INTO TALLESPORARTICULOS (ProductId, Prodposition, Sizes) VALUES(1, 4, 'XTRALARGE')
1 rows affected

INSERT INTO TALLESPORARTICULOS (ProductId, Prodposition, Sizes) VALUES(1, 5, 'XXTRALARGE')
1 rows affected

INSERT INTO TALLESPORARTICULOS (ProductId, Prodposition, Sizes) VALUES(2, 1, 'SMALL')
1 rows affected

INSERT INTO TALLESPORARTICULOS (ProductId, Prodposition, Sizes) VALUES(2, 2, 'MEDIUM')
1 rows affected

INSERT INTO TALLESPORARTICULOS (ProductId, Prodposition, Sizes) VALUES(2, 3, 'LARGE')
1 rows affected

INSERT INTO TALLESPORARTICULOS (ProductId, Prodposition, Sizes) VALUES(2, 4, 'XTRALARGE')
1 rows affected

INSERT INTO TALLESPORARTICULOS (ProductId, Prodposition, Sizes) VALUES(2, 5, 'XXTRALARGE')
1 rows affected

INSERT INTO TALLESPORARTICULOS (ProductId, Prodposition, Sizes) VALUES(3, 1, '02');
1 rows affected

INSERT INTO TALLESPORARTICULOS (ProductId, Prodposition, Sizes) VALUES(3, 2, '04');
1 rows affected

INSERT INTO TALLESPORARTICULOS (ProductId, Prodposition, Sizes) VALUES(3, 3, '06');
1 rows affected

INSERT INTO TALLESPORARTICULOS (ProductId, Prodposition, Sizes) VALUES(3, 4, '08');
1 rows affected

CREATE TABLE RANGOSTALLE (
  ProductId INTEGER NOT NULL,
  FromPosition INTEGER NOT NULL,
  ToPosition INTEGER NOT NULL,
  Price double precision not null
  );
INSERT INTO RANGOSTALLE (ProductId,FromPosition,ToPosition,Price) VALUES (1, 1,3,500);
1 rows affected

INSERT INTO RANGOSTALLE (ProductId,FromPosition,ToPosition,Price) VALUES (1, 4,5,600);
1 rows affected

INSERT INTO RANGOSTALLE (ProductId,FromPosition,ToPosition,Price) VALUES (2, 1,3,500);
1 rows affected

INSERT INTO RANGOSTALLE (ProductId,FromPosition,ToPosition,Price) VALUES (2, 4,5,600);
1 rows affected

INSERT INTO RANGOSTALLE (ProductId,FromPosition,ToPosition,Price) VALUES (3, 1,4,200);
1 rows affected

select substring(LongSKU from 1 for 9), low.sizes, high.sizes, avg(price)
from articulos join RANGOSTALLE on articulos.ProductId = RANGOSTALLE.ProductId
join TALLESPORARTICULOS low on RANGOSTALLE.ProductId = low.ProductId and RANGOSTALLE.FromPosition = low.Prodposition
join TALLESPORARTICULOS high on RANGOSTALLE.ProductId = high.ProductId and RANGOSTALLE.ToPosition = high.Prodposition
group by 1,2,3


SUBSTRING
SIZES
SIZES
AVG


A22121000
SMALL
LARGE
500

A22121000
XTRALARGE
XXTRALARGE
600

A22123001
02
08
200
SUBSTRING	SIZES	SIZES	AVG
A22121000	SMALL	LARGE	500
A22121000	XTRALARGE	XXTRALARGE	600
A22123001	02	08	200