--- In firebird-support@yahoogroups.com, Johan wrote:

> --- In firebird-support@yahoogroups.com, Lucas Franzen <luc@r...>
> Hmm...it's even worse I think! In my example I used xxxx and yyyy

but

> in real life it's xxxx i want in the first column and whatever else
> in the second column. The yyyy can be infinit number of variants!

Is what you want something like

select distinct dlink, regdate, worker, wtype,
(select t2.nett*t2.pcs from table1 t2
where t2.dlink = t1.dlink
and t2.regdate = t1.regdate
and t2.worker = t1.worker
and t2.wtype = t1.wtype
and t2.artno = 'xxxx') as xxxx_nett,
(select t2.gross*t2.pcs from table1 t2
where t2.dlink = t1.dlink
and t2.regdate = t1.regdate
and t2.worker = t1.worker
and t2.wtype = t1.wtype
and t2.artno = 'xxxx') as xxxx_gross,
(select t2.nett*t2.pcs from table1 t2
where t2.dlink = t1.dlink
and t2.regdate = t1.regdate
and t2.worker = t1.worker
and t2.wtype = t1.wtype
and t2.artno <> 'xxxx' ) as other_nett,
(select t2.gross*t2.pcs from table1 t2
where t2.dlink = t1.dlink
and t2.regdate = t1.regdate
and t2.worker = t1.worker
and t2.wtype = t1.wtype
and t2.artno <> 'xxxx') as other_gross,
from table1 t1

Set

> I'm thinking of doing a datamoving routine before my actual sql that
> moves each row into a new table and in that move put it in column

one

> or two and do a per row sum also.
>
> After that it should be straigh forward for a sql query and

grouping.

>
> Cheers
> /Johan