Transpose Table Function

Here's a generic transpose function. It will transform the first 254 rows of any table into columns and use column 1 for the original header names. Just change the "MyTable" to your desired target table.

DO transpose WITH "MyTable"
BROWSE NOWAIT

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
PROCEDURE transpose
PARAMETERS lcMyTable

lctalk=SET("Talk")

SET TALK OFF

IF USED(lcMyTable)=.f.
USE (lcMyTable) IN 0
ENDIF

SELECT (lcMyTable)

lnMaxWidth=1
lcBuildTable=""

FOR lnFieldCounter = 1 TO AFIELDS(laFields)
lnMaxWidth=MAX(lnMaxWidth,laFields(lnFieldCounter,3))
ENDFOR

FOR lnRowCounter = 1 TO MIN(RECCOUNT(),254)
lcBuildTable=lcBuildTable+IIF(lnRowCounter=1,"",",")+" COL"+TRANSFORM(lnRowCounter)+" C("+TRANSFORM(lnMaxWidth)+")"
ENDFOR

CREATE TABLE tbl_transformed (Headers c(10),&lcBuildTable)
FOR lnRecords= 1 TO MAX(1,lnFieldCounter-1)
APPEND BLANK
ENDFOR

SELECT (lcMyTable)
lcColsToTrans=AFIELDS(laFields)
SCAN FOR RECNO()<=254
lnTransCol=RECNO()
FOR lnCounter = 1 TO lcColsToTrans
lcVar="Var"+TRANSFORM(lnCounter)
&lcVar=EVALUATE(laFields(lnCounter,1))
ENDFOR

SELECT tbl_transformed

FOR lnCounter2 = 1 TO lcColsToTrans
GO lnCounter2
lcVar="Var"+TRANSFORM(lnCounter2)
REPLACE (FIELD(lnTransCol+1)) WITH TRANSFORM(&lcVar)
ENDFOR
ENDSCAN

SELECT tbl_transformed
FOR lnCounter = 1 TO lcColsToTrans
GO lnCounter
REPLACE (FIELD(1)) WITH (laFields(lnCounter,1))
ENDFOR
GO TOP
SET TALK &lctalk
ENDPROC