Hi,

I have following tables:

Table A

=====================

Item, Onstock,Ordered

ABC        10            1

CDF        100          10


Table B

===================

Item, Onstock,Ordered, Location

ABC        10            1                1

ABC        100          15              2

CDF        200          50              1

CDF        100          10              2

Table C

===================

Location, Name

1              Copenhagen

2              London


I want following output:

Item, Onstock_Copenhagen, Ordered_Copenhagen,Onstock_London,Ordered_London

====================================================================

ABC                    10                                     1        
                   100                    15

CDF                    200 50                            100           
        10

I've tried different syntaxes, joins but cannot get expected result. Can
anyone help with this?

Thanks in advance,

Hüseyin

Hüseyin

1.
II don't know why but there are lots of  in your post - makes it difficult to read. I've removed them in notepad so I can have a look.

2. can you post what you've already tried

Roy Lambert

Hüseyin



What is the correct output using the figures you've shown?

What is the relevance of TableA?
What is the relevance of TableC?
For the output as you show it neither TableA nor TableC are required.

Are there only ever going to be two locations or can there be more? If there are a fixed number of locations its relatively simple. If the number is not defined then its far more difficult.


Roy Lambert

Roy,

Thanks for your input. Table A contains items info, Table C contains
locations (store's if you may say). Locations are not fixed numbers and
can be as many as user defines them. In short i want to show information
about items - one row for every item, how many on stock and how many
ordered - on each location - but in a single row.

I have tried following:

select
  a.Itemno, concat(C.Name with ' - ' + CAST(Onstock AS varCHAR(30))) as
"On stock",concat(C.Name with ' - ' + CAST(coalesce(Ordered,0) AS
varCHAR(30))) as "Reserved",C.Name
from
  items a
  inner join locationitems b on a.itemno = b.itemno
  inner join locations c on b.location = c.location
  where a.itemno = b.itemno
group by
  a.itemno,"On stock",Name

This gives me a row for every location, i want a row for every itemno.
Hope it's more clear

Thanks,

Hüseyin



Den 04-05-2018 kl. 14:58 skrev Roy Lambert:
> Hüseyin
>
>
>
> What is the correct output using the figures you've shown?
>
> What is the relevance of TableA?
> What is the relevance of TableC?
> For the output as you show it neither TableA nor TableC are required.
>
> Are there only ever going to be two locations or can there be more? If there are a fixed number of locations its relatively simple. If the number is not defined then its far more difficult.
>
>
> Roy Lambert
>

Hüseyin


DBISAM doesn't support that type of processing. What you're looking for is a pivot table or crosstab and that's not available in DBISAM.

There are three options:

1. look at third party components (eg https://github.com/ccy/txquery)
2. use a mixed Delphi and SQL solution
3. a Delphi only solution

Personally I'd go for option 3. As long as you don't have any visual components a pure Delphi solution can be pretty fast.

If you decide on option 2 the approach would be to use Delphi to create a DBISAM script to do the work.

Use TableA and TableC to generate an INSERT INTO Memory\TableD statement along the lines of

sl:=TStringList.Create;
sl.Add('SELECT Item,');
TableC.First;
while not TableC.Eof do begin
sl.Add('0 AS 'OnStock'_+TableC.FieldByName('Name').AsString+'0 AS Ordered_'+TableC.FieldByName('Name').AsString);
sl.Add(',');
TableC.Next;
end;
sl.Delete(sl.Count-1);
sl.Add('INTO Memory\TableD' FROM TableA;');

That should set up a table to accept all the information. Then you need an additional statement for each location.

TableC.First;
while not TableC.Eof do begin
sl.Add(UPDATE Memory\TableD SET 'OnStock_'+TableC.FieldByName('Name').AsString+'=TableBOnStock, Ordered_'+TableC.FieldByName('Name').AsString+'='TableBOrderd JOIN TableB ON TableB.Item = Memort\TableD.Item AND TableB.Location = '+TableC.FieldByName('Location').AsString+';');
TableC.Next;
end;

Then assign sl to a query and run it.

I think it should work but its just typed in here and totally untested.

Roy Lambert

Hüseyin


Out of interest is this going to be a printed report, an on-screen report or dumped into something like Excel?

Roy Lambert

Hi Roy,

Thank you, it will try your suggestion. Your post also gave me some
other ideas, i have Reportbuilder which includes a crossreport
component, maybe it can be used for this purpose also.

Thanks,

Hüseyin


Den 05-05-2018 kl. 15:19 skrev Roy Lambert:
> Hüseyin
>
>
> DBISAM doesn't support that type of processing. What you're looking for is a pivot table or crosstab and that's not available in DBISAM.
>
> There are three options:
>
> 1. look at third party components (eg https://github.com/ccy/txquery)
> 2. use a mixed Delphi and SQL solution
> 3. a Delphi only solution
>
> Personally I'd go for option 3. As long as you don't have any visual components a pure Delphi solution can be pretty fast.
>
> If you decide on option 2 the approach would be to use Delphi to create a DBISAM script to do the work.
>
> Use TableA and TableC to generate an INSERT INTO Memory\TableD statement along the lines of
>
> sl:=TStringList.Create;
> sl.Add('SELECT Item,');
> TableC.First;
> while not TableC.Eof do begin
> sl.Add('0 AS 'OnStock'_+TableC.FieldByName('Name').AsString+'0 AS Ordered_'+TableC.FieldByName('Name').AsString);
> sl.Add(',');
> TableC.Next;
> end;
> sl.Delete(sl.Count-1);
> sl.Add('INTO Memory\TableD' FROM TableA;');
>
> That should set up a table to accept all the information. Then you need an additional statement for each location.
>
> TableC.First;
> while not TableC.Eof do begin
> sl.Add(UPDATE Memory\TableD SET 'OnStock_'+TableC.FieldByName('Name').AsString+'=TableBOnStock, Ordered_'+TableC.FieldByName('Name').AsString+'='TableBOrderd JOIN TableB ON TableB.Item = Memort\TableD.Item AND TableB.Location = '+TableC.FieldByName('Location').AsString+';');
> TableC.Next;
> end;
>
> Then assign sl to a query and run it.
>
> I think it should work but its just typed in here and totally untested.
>
> Roy Lambert
>

Hi Roy,

Actually all 3 options are valid

Regards,

Hüseyin


Den 06-05-2018 kl. 08:45 skrev Roy Lambert:
> Hüseyin
>
>
> Out of interest is this going to be a printed report, an on-screen report or dumped into something like Excel?
>
> Roy Lambert
>

Hüseyin


>Actually all 3 options are valid

In that case get ready for complaints about it being unreadable

Roy

Hi Roy,



Regards,

Hüseyin


Den 06-05-2018 kl. 15:48 skrev Roy Lambert:
> Hüseyin
>
>
>> Actually all 3 options are valid
> In that case get ready for complaints about it being unreadable
>
> Roy