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

Paul,

* Create cursors
* states tables might already be there
* let's create if not
lcSTATES = "AK,AL,AR,AZ,CA,CO,CT,DC,"+;
  "DE,FL,GA,HI,IA,ID,IL,IN,KS,KY,"+;
  "LA,MA,MD,ME,MI,MN,MO,MS,MT,NC,"+;
  "ND,NE,NH,NJ,NM,NV,NY,OH,OK,OR,"+;
  "PA,PR,RI,SC,SD,TN,TX,UT,VA,VT,WA,WI,WV,WY"
Alines(aStates,m.lcStates,.T.,',')
Dimension aStates[Alen(aStates),1]

Create Cursor states (state c(2))
Append From Array aStates

Create Cursor dummy (trno i)
For ix = 1 To 1000
  Insert Into dummy Values (m.ix)
Endfor

Select Top 30000 Padl(trNo,5,'0') As TransitNo,state As Region,Rand() ;
  from states,dummy ;
  order By 3 ;
  into Cursor MyRecs

Create Cursor transits (transitNo c(5), Region c(2))
Append From Dbf('MyRecs')
*Select * from transits order by transitNo
* Create cursors

xTabTransits()
Select xTabbed
Browse

Function xTabTransits && cross tab transits
  Local Array aStates[1]
  Select state,'C',3,0 From states Into Array aStates
  Local Array aStruc[Alen(aStates,1)+1,4],aDataRec[1,alen(aStates,1)+1]
  Acopy(aStates,aStruc,1,-1,5)
  aStruc[1,1] = "TransitNo"
  aStruc[1,2] = "C"
  aStruc[1,3] = 5
  aStruc[1,4] = 0
  aDataRec = ''
  Create Cursor xtabbed From Array aStruc
  Select transitNo,Region From transits Order By 1,2 Into Cursor crsTransits
  Scan
    If !(transitNo == aDataRec[1,1])
      aDataRec = ''
      aDataRec[1,1] = transitNo
      Scan While transitNo == aDataRec[1,1]
        aDataRec[1,Ascan(aStruc,region,1,-1,1,1+2+4+8)] = 'Yes'
      Endscan
      Insert Into xtabbed From Array aDataRec
      Skip -1
    Endif
  Endscan
Endfunc

Cetin