Database SQL Tips
The Assets Crosstab sample tables
as SQL statements and as CSV-files (zipped).
Creating a Crosstab or Pivot table using generic transferrable SQL
Though some DBMS do have specific crosstab or pivot table features the following SQL favours a transparant generic code easily transferrable between most standard database systems.
Applies to SQL Server, MySQL, PostgreSQL, SQLite, Absolute Database, SQLMemTable and others.
See code examples.
Creating a Crosstabs with Paradox using stored SQL's equivalating VIEWs
Though Paradox basically does not support crosstab or pivot table views, creating crosstabs in Paradox actually is possible with a few basic tricks.
Screenprint from Delphi Paradox application:
See code example.
UPDATE table SET column = (SELECT ...
How to update a table from values of another table using a correlated subquery without NULL'ing non-matching records.
UPDATE tablename SET column = (SELECT ...
Applies to Paradox, dBase, SQL Server, MySQL, PostgreSQL, SQLite, InterBase, Absolute Database, SQLMemTable and others.
See code example.
UPDATE table from other table without subquery
How to update a table from values of another table without using a correlated subquery and without NULL'ing non-matching records.
UPDATE tablenameA SET tablenameA.column = tablenameB.column FROM ...
Applies to SQL Server, MySQL, DBISAM and others.
See code example.