>>>Hi all,
>>>
>>>I have mytable.dbf that contains 2 fields :
>>>
>>>transitNo region
>>>
>>>00001 NY
>>>00001 CA
>>>00001 FL
>>>00001 CO
>>>
>>>00002 CA
>>>00002 NY
>>>00002 FL
>>>00002 CO
>>>
>>>How to use SQL statments to have newtable ? with 5 fields like Pivot table
>>>in Access or Excel to become :
>>>
>>>Newtable :
>>>
>>>transitNo NY CA FL CO
>>>00001 yes yes yes yes
>>>
>>>00002 yes yes yes yes
>>>
>>>Tia for any help in coding with SQL .
>>
>>
>>If you didn't know beforehand how many regions you could have in table, you could try this:
>>

>>CREATE CURSOR MyTable (transitNo C(10), region C(2))
>>INSERT INTO  MyTable  VALUES('00001','NY')
>>INSERT INTO  MyTable  VALUES('00001','CA')
>>INSERT INTO  MyTable  VALUES('00001','FL')
>>INSERT INTO  MyTable  VALUES('00001','CO')
>>
>>INSERT INTO  MyTable  VALUES('00002','CA')
>>INSERT INTO  MyTable  VALUES('00002','NY')
>>INSERT INTO  MyTable  VALUES('00002','WA') && I changed this just for testing
>>INSERT INTO  MyTable  VALUES('00002','CO')
>>
>>SELECT DISTINCT region FROM MyTable INTO CURSOR crsDistReg
>>lcSelect = [SELECT TransitNo]
>>SELECT crsDistReg
>>SCAN
>>   lcSelect = lcSelect + [, MAX(IIF(Region = ']+Region+[','Yes','No ')) AS ]+Region
>>ENDSCAN
>>lcSelect = lcSelect +;
>>    [ FROM MyTable GROUP BY TransitNo INTO CURSOR crsXTab]
>>&lcSelect
>>BROWSE NORMAL
>>

>
>Boris,
>Luckily his regions are states:)
>Cetin


Who knows, maybe after a while some other state will come :o))