Hi,

I have the following records

EmpNbr,Date,Avail,Value

eg 0001,01/03/09,AM,True
   0001,01/03/09,PM,False
   0001,01/03/09,ND,True

as 3 records.

I want to merge these thre records grouped by empnbr and date into 1 record

EmpNbr,Date,AvailAM,AvailPn,AvailND

eg 0001,01/03/09,True,False,True

Anyone know how this might be possible, SQL script allowed and DBISAM V3.30

Thanks

Alex

"Al Vas" <noreply@noreply.com> wrote in message
news:D838A9D9-846A-42F9-85A8-4431808610C9@news.elevatesoft.com...
> Hi,
>
> I have the following records
>
> EmpNbr,Date,Avail,Value
>
> eg 0001,01/03/09,AM,True
>    0001,01/03/09,PM,False
>    0001,01/03/09,ND,True
>
> as 3 records.
>
> I want to merge these thre records grouped by empnbr and date into 1
> record
>
> EmpNbr,Date,AvailAM,AvailPn,AvailND
>
> eg 0001,01/03/09,True,False,True
>
> Anyone know how this might be possible, SQL script allowed and DBISAM
> V3.30
>

SELECT DISTINCT EmpNbr, Date, CAST(NULL as Boolean)  AvailAM, CAST(NULL as
Boolean) AvailPM, Cast(Null as Boolean) AvailND
INTO memory  temp
from mytable;
/*create index if table is big*/
UPDATE memory temp M
SET AvailAM  = Value
FROM memory temp M
JOIN mytable T on ((T.EmpNbr = M.EmpNbr) and (t.Date = m.Date))
WHERE Avail = 'AM';

same for the other two.

Robert