majstoru wrote:

> Hi,
>
> I have a data in table (example)
> Table1
> id name qty
> 1 Article 1 3
> 2 Article 2 2
> 3 Article 3 1
>
> is there any solution if Firebird that I can write SQL statement which
> will return this table in format
> Table2
> id name
> 1 Article 1
> 1 Article 1
> 1 Article 1
> 2 Article 2
> 2 Article 2
> 3 Article 3
>
> whih mean that I get result table where avery record is multiplied
> with qty, or how many rows are in resultset, that is represent a qty
> column in table1.
>
> Thanks for help!
>
>

only using an SP

something like this:

create procedure ArticleMuliply returns (ID integer, Name varchar(40)) as

declare variable wCount integer;

begin
for
select
ID, Name, Qty
from
Articles
order by
ID
into
:ID, :Name, :wCount
do begin
While (wCount > 0) do begin
Suspend;
wCount = wCount -1;
end
end
end

another less elegant method will be have an auxiliary table containing
values from 1 to the maximum quantity you expect to have like this:

Create Table AuxQty (Qty integer);

insert into AuxQty values (1);
insert into AuxQty values (2);
insert into AuxQty values (3);
.....
insert into AuxQty values (9999);


and use an statement like this

select
A.ID, A.Name
from
Articles A join
AuxQty on (AuxQty.Qty <= A.Qty)
order by
A.ID

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br