>Can we do cross tab query using SQL only? no _GENXTAB
>
>I want to do the following"
>
>city1 date1
>city1 date2
>city1 date3
>city2 date1
>city2 date2
>
>to
>city1 date1 date2 date3
>city2 date1 date2
>
>Mo

CREATE CURSOR origin (pivot C(20),info C(20))
INSERT INTO origin VALUES ('city1','date1')
INSERT INTO origin VALUES ('city1','date2')
INSERT INTO origin VALUES ('city1','date3')
INSERT INTO origin VALUES ('city2','date1')
INSERT INTO origin VALUES ('city2','date2')

#DEFINE WITH_A_BETTER_VFP .F.

IF WITH_A_BETTER_VFP
	SELECT pivot;
	, MAX(ICASE(Y.iCol = 1,O.info)) Col1;
	, MAX(ICASE(Y.iCol = 2,O.info)) Col2;
	, MAX(ICASE(Y.iCol = 3,O.info)) Col3;
	, MAX(ICASE(Y.iCol = 4,O.info)) Col4;
	FROM origin O ;
	JOIN (SELECT RECNO() iCol, info FROM (SELECT info FROM origin GROUP BY info ORDER BY info) X) Y;
	ON Y.info = O.info;
	GROUP BY 1;
	ORDER BY 1
ELSE
	SELECT pivot;
	, MAX(ICASE(Y.iCol = 1,O.info,CAST(NULL AS C(20)))) Col1;
	, MAX(ICASE(Y.iCol = 2,O.info,CAST(NULL AS C(20)))) Col2;
	, MAX(ICASE(Y.iCol = 3,O.info,CAST(NULL AS C(20)))) Col3;
	, MAX(ICASE(Y.iCol = 4,O.info,CAST(NULL AS C(20)))) Col4;
	FROM origin O ;
	JOIN (SELECT RECNO() iCol, info FROM (SELECT info FROM origin GROUP BY info ORDER BY info) X) Y;
	ON Y.info = O.info;
	GROUP BY 1;
	ORDER BY 1
ENDIF