>>>>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))

Then he'd need to adjust the code to check commandline limit:)
Cetin