Topic: SQL copy with case or if?

Hi MVD,

Right now I have table like this:

Orders;
OrderNO.          Item                  Size       Quantity
1111111             ITEM22                 28            3
2222222            ITEM18                 22            4
333333              ITEM22                 26            3
1111111              ITEM22                 22            1
1111111              ITEM22                 26            2


I would like to copy these orders to other table that look like this:
OrderFiltered:
OrderNO:           Item                  22           26        28         
1111111             ITEM22               1             2         3
2222222            ITEM18              4             0         0
3333333           ITEM22               0             3         0

Thank You for all Your help

2 (edited by CDB 2020-06-22 19:33:23)

Re: SQL copy with case or if?

Argonex,


I assume you are displaying the results in a table grid.  If I understand your question correclty, all you need to do is set the 'sort' option in the table grid settings, see my attached picture.

You might have to also write into the filter option GROUP BY OrderNo so you only see each item once.


I think to fully get where you want to be you are going to have to write your own SQL script, I'll have a think on that.

Post's attachments

Attachment icon sort and group..PNG 144.62 kb, 136 downloads since 2020-06-22 

On a clear disk you can seek forever

Re: SQL copy with case or if?

Ty CDB,

I believe grouping is not an option because i want to track deliveries later. Simple order, deliver sistem but only this i get trouble with...and also I'm total newbie with databases :'(

I learned so far insert, delete, update, simple queries...

Something what I'am looking for is something like
INSERT into OrderFiltered (OrderNO, Item, 22, 24, 26) VALUES ($value1, $value2, $value3, $value4, $value5)
But before that I need something to define values
1 to value5

value3 = if OrderNO.Size = 22 copy AMOUNT into OrderFiltered.22 else insert 0

TY

Re: SQL copy with case or if?

Just if someone need answer

Insert INTO TABLE1 (column1, column2, column3)
SELECT DISTINCT 
         column1 as column1,
         column2 as column2,
         max(case when column4 = 20 then column5 else 0 end) as column3,
 
from TABLE2

GROUP BY column1, column2, column3