WOW!  Great info!  Thanks!!

On 6/18/2020 9:49 AM, Thies Grimm wrote:
> hi,
> I found an example at	http://www.nknabe.dk/database/sql/code/SQL_crosstab_Paradox.htm
> 
> I made an example Script
> 
> looks nice, doesn't it?
> Thies
> 
> create a new script ant put the following code in it:
> 
> **********************************************************
> 
> ; //	http://www.nknabe.dk/database/sql/code/SQL_crosstab_Paradox.htm
> ; //	Create Tables by Thies Grimm
> ; //	
> proc cmExecuteSQLString(
> 				var db dataBase,
> 				stSQL	String
> 				) Logical
> var
> 	itemSQL				SQL
> endVar
> 	itemSQL.readFromString(stSQL)
> 	return cmExecuteSQL(db, itemSQL)
> endProc
> 				
> proc cmExecuteSQL(
> 				var db dataBase,
> 				var itemSQL	SQL
> 				) Logical
> 	if not itemSQL.executeSQL(db) then
> 		return False
> 	endIf
> 	return True
> endProc
> 
> proc cmExecuteSQL2TC(
> 				var db dataBase,
> 				var itemSQL	SQL,
> 				var tc TCursor
> 				) Logical
> 	if not itemSQL.executeSQL(db, tc) then
> 		return False
> 	endIf
> 	return True
> endProc
> 
> method run(var eventInfo Event)
> var
> 	dynTblDef			dynArray[] String
> 	dynTblValues		dynArray[] String
> 	fs						FileSystem
> 	tbl					Table
> 	element				String
> 	ar						Array[] String
> 	si						Smallint
> 	db						DataBase
> 	itemSQL				SQL
> 	tc						TCursor
> 	tv						TableView
> endVar
> 
> 	db.open("priv")
> 
> ; //	Table Definitions
> 	dynTblDef["__Assets.db"] 		 	= "AssetID INTEGER, AssetType INTEGER, AssetStatus
> INTEGER, PRIMARY KEY(AssetID, AssetType)"
> 	dynTblDef["__AssetTypes.db"]  	= "ID INTEGER, TypeName CHAR(10), PRIMARY
> KEY(ID)"
> 	dynTblDef["__AssetStatus.db"] 	= "ID INTEGER, StatusName CHAR(10), PRIMARY
> KEY(ID)"
> 
> ; //	Values
> 	dynTblValues["__AssetStatus.db"] = "1, 'Deployed'|2, 'Inventory'|3,'Shipped'"
> 	dynTblValues["__AssetTypes.db"] 	= "1, 'Desktop'|2, 'Laptop'|3,'Server'"
> 	dynTblValues["__Assets.db"] 		= "1010,1,2|1011,1,2|1012,1,2|1013,3,1|1014,1,3|1015,1,2|1016,1,2|1017,2,2|1018,1,1|1019,1,2"
> +
> 												  "|1020,1,2|1021,1,2|1022,1,2|1023,3,1|1024,1,3|1025,1,2|1026,1,2|1027,2,2|1028,1,1|1029,1,2"
> +
> 												  "|1030,1,2|1031,1,2|1032,1,2|1033,3,1|1034,1,3|1035,1,2|1036,1,2|1037,2,2|1038,1,1|1039,1,2"
> 
> 
> 	if not isDir(":priv:\\SQL") then
> 		fs.makeDir(":priv:\\SQL")
> 	endIf
> 
> ; //	Create Tables
> 	forEach element in dynTblDef
> 		tbl.attach(element, db)
> 		if tbl.isTable() then
> 			tbl.delete()
> 		endIf
> 		stSQL = "CREATE TABLE '" + element + "' (" + dynTblDef[element] + ")"
> 		if not cmExecuteSQLString(db, stSQL) then
> 			errorshow()
> 			return
> 		endIf
> 		dynTblValues[element].breakapart(ar, "|")
> 		for si from 1 to ar.size()
> 			stSQL = "INSERT INTO '" + element + "' VALUES(" + ar[si] + ")"
> 			if not cmExecuteSQLString(db, stSQL) then
> 				errorshow()
> 				return
> 			endIf
> 		endFor
> 	endForEach
> 
> 
> ; //	First we will generate a de-coded Assets list for simpler access for
> the successive queries:
> ; "/* This is the AssetsDecoded.sql extracting Assets decoded for TypeName
> and Status */ " +
> 	stSQL = "/* This is the AssetsDecoded.sql extracting Assets decoded for
> TypeName and Status */ \n" +
> 			"SELECT DISTINCT  T.TypeName , S.StatusName , A.AssetID \n" +
> 			"FROM '__AssetTypes.db' T \n" +
> 			" LEFT JOIN '__Assets.db' A \n" +
> 			"	ON A.AssetType = T.ID \n" +
> 			" JOIN '__AssetStatus.db' S \n" +
> 			"	ON S.ID = A.AssetStatus	\n"
> 	itemSQL.ReadFromString(stSQL)
> 	itemSQL.writeSQL(":priv:SQL\\AssetsDecoded.sql")
> 
> 
> 	stSQL = "/* This is the AssetsXTab0.sql generating the left hand categories
> */ \n" +
> 			"SELECT DISTINCT TypeName \n" +
> 			"FROM '__AssetTypes.db' "
> 	itemSQL.ReadFromString(stSQL)
> 	itemSQL.writeSQL(":priv:SQL\\AssetsXTab0.sql")
> 
> ; //	alle StatusName ermitteln für Columns
> 	stSQL = "SELECT DISTINCT StatusName \n FROM '__AssetStatus.db'"
> 	itemSQL.ReadFromString(stSQL)
> 	if not cmExecuteSQL2TC(db, itemSQL, tc) then
> 		errorShow()
> 		return
> 	endIf	
> 
> ; //	je Spalte(Column) ein SQL Statement
> 	scan tc:
> 		st = tc."StatusName"
> 		stSQL = "/* This is the AssetsXTab" + st + ".sql generating the " + st
> + " aggregate column */ \n" +
> 				"SELECT DISTINCT TypeName \n" +
> 				" \t\t, COUNT(AssetID) AS " + st + "\n" +
> 				" FROM SQL\\AssetsDecoded.sql \n" +
> 				"WHERE \n" +
> 				" StatusName = '" + st +"' \n" +
> 				"GROUP BY TypeName "
> 		itemSQL.ReadFromString(stSQL)
> 		itemSQL.writeSQL(":priv:SQL\\AssetsXTab" + st + ".sql") 		
> 	endScan
> 
> ; // ein zusammenfassendes Statment für Total
> 	stSQL = "/* This is the AssetsXTabDTotal.sql generating the Totals aggregate
> column */ \n" +
> 			"SELECT DISTINCT TypeName \n" +
> 			" \t\t, COUNT(AssetID) AS Total \n" +
> 			" FROM SQL\\AssetsDecoded.sql \n" +
> 			"GROUP BY TypeName "
> 	itemSQL.ReadFromString(stSQL)
> 	itemSQL.writeSQL(":priv:SQL\\AssetsXTabDTotal.sql")
> 
> 
> ; //	And to run the final crosstab query just execute this:
> 	stSQL =
> 		; //	"/* This is AssetsXTabFinal.sql calling all other SQLs and generating
> final crosstab */ " +
> 		" SELECT DISTINCT a.TypeName \n"
> 		scan tc:
> 			st = tc."StatusName"
> 			stSQL = stSQL + " \t\t, " + st + "\n"
> 		endScan
> 		stSQL = stSQL + " \t\t, Total \n FROM SQL\\AssetsXTab0.sql a \n"
> 		scan tc:
> 			st = tc."StatusName"
> 			stSQL = stSQL + "   LEFT JOIN SQL\\AssetsXTab" + st + ".sql x" + st +
> " ON x" + st + ".TypeName = a.TypeName" + "\n"
> 		endScan
> 		stSQL = stSQL +"   LEFT JOIN SQL\\AssetsXTabDTotal.sql xTotal ON xTotal.TypeName
> = a.TypeName"
> 	itemSQL.readFromString(stSQL)
> 	itemSQL.writeSQL(":priv:SQL\\AssetsFinal.sql")
> 			
> 	if not cmExecuteSQL2TC(db, itemSQL, tc) then
> 		errorShow()
> 		return
> 	endIf
> 
> 	tc.instantiateView(":priv:__tblCrosstabAsset.db")
> 	tc.close()
> 
> 	tv.open(":priv:__tblCrosstabAsset.db")
> 
> 
> endMethod
> 
> 
> **********************************************************
> 
> 
> Anders Jonsson <andersREMOVETHIS@jonssondata.se> wrote:
>>
>>
>>> But surely ObjectPAL does CROSSTAB (like pdoxDOS did)?
>>
>> It does but not as easily as in DOS!
>>
>> It was a long time since I did this but if I remember correctly you need
> 
>> to first create a Crosstab in a a form and then you can use the
>> AcctionDataCommand DataSaveCrosstab for the uibojcet which will create a
> 
>> table CROSSTAB.DB in PRIV.
>>
>> I used something like this:
>>
>> Open a hidden form with the crosstab
>>
>> Issue uiCrossTab.action(DataSaveCrosstab)
>>
>> Close the hidden form
>>
>> Work with the Crosstab table.
>>
>> There are probably a better way but that was how I solved it a long time
> 
>> ago...
>>
>>
>