>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