FB3.0 SQL

[Henry Olive 5]

I wish everyone a healthy day.

 

ITEMS Table

ITEMNO

AAA

 

ITEMMOVEMENTS View  ( WHNO = Ware House No, and i dont know how many )
ITEMNO...WHNO...INCOME...OUTGO

AAA.............1................10.................0
AAA.............2................20.................0
AAA.............3................30.................0
AAA.............3..................0...............10

 

How can get below result ?
ITEMNO...TOTAL_STOCK.....1.......2.......3
AAA...............50......................10.....20.....20
 

Thank You

 

[Attila Kovacs 629]

aggregate + pivot

 

not sure that FB3 supports the latter though

[Serge_G 87]

Well there is LIST but, I am not sure you agree the result (even if you can work with via a TStringList)

PIVOT, I don't think it's supported even in FB4.

I see that it is a view, don't you think that using the original table should be better?

 

NOTE : Please for SQL questions, furnish a script for creating table, and filling it. Something like 
 

CREATE TABLE TESTHO(
ITEMNO VARCHAR(10),
WHNO INTEGER,
INCOME INTEGER,
OUTGO INTEGER);
COMMIT;
INSERT INTO TESTHO VALUES('AAA',1,10,0);
INSERT INTO TESTHO VALUES('AAA',2,20,0);
INSERT INTO TESTHO VALUES('AAA',3,30,0);
INSERT INTO TESTHO VALUES('AAA',3,0,10);

this to avoid us the "coding" for the test.  

 

With LIST you can do something like

SET TERM !! ;

EXECUTE BLOCK
RETURNS ( 
    ITEMNO VARCHAR(10),
    TOTAL INTEGER, 
    WHNOS VARCHAR(250), 
    QTYS VARCHAR(250)
)
AS
BEGIN
    SELECT ITEMNO,SUM(INCOME-OUTGO) FROM TESTHO GROUP BY ITEMNO INTO ITEMNO,TOTAL;
    WITH C AS (SELECT ITEMNO,WHNO, SUM(a.INCOME-a.OUTGO) Q FROM TESTHO a GROUP BY ITEMNO,WHNO)
          SELECT LIST(WHNO,';'),LIST(Q,';')  FROM C a GROUP BY ITEMNO
          INTO WHNOS,QTYS; 
    SUSPEND;
END!!

SET TERM ; !!

And obtain

ITEMNO    TOTAL    WHNOS    QTYS
AAA               50    1;2;3         10;20;20

but your test data is too small and don't expect order in both lists always correct

 

 

Edited November 29, 2021 by Serge_G

[Vandrovnik 214]

As far as I know, Firebird does not support PIVOT.

If you really need a column for each WHNO, you can first use a query to get distinct WHNO values and then use these values to create second query, which would compute the values.

[Stano 143]

Or

Five methods converting rows to columns.txt

[Henry Olive 5]

Thank you so much  Attila, Serge, Vandrovnik, Stano
 

Serge, your solution is very interesting, not exactly like Pivot but it is very very simple and fast, i'll keep your codes for future

Stano, Your five methods... is also very very useful

Thank you so so much