FreeTDS Development Group

["James K. Lowden" <jklowden AT freetds.org>]

Tonight's CVS HEAD snapshot contains a little diversion of mine, a new
db-lib function: dbpivot().  Documentation is not yet ready; see the
new bsqldb.1 in
http://freetds.cvs.sourceforge.net/viewvc/freetds/freetds/src/dblib/
and look for "dbpivot" in the bsqldb source code.  

dbpivot() provides a facility similar to the T-SQL PIVOT operator,
which is similar to Excel's PivotTable.  Unlike the T-SQL version,
dbpivot() does not require the programmer to specify the pivot values.
Like Excel, the caller specifies the *columns* whose values become the
names of the pivoted columns.  Unlike Excel, it doesn't require Excel.  

The first application to use dbpivot() -- I have it on good authority
-- is bsqldb.  That is because your humble maintainer has often felt the
need for it in batch processing, and has never been delighted at the
dynamic-SQL shennanigans required.  

Prior to devoting two weekends to 1314 lines of brand-new C, I did poke
around the interwebs for at least 15 minutes looking for a good
general-purpose result-set pivoting utility.  (If you know of one, I'd
be interested to learn.)  But the best solution really is a library
function because

1.  The client library has the metadata.  
2.  The client library allows the user to specify a callback
aggregate function.  
3.  The client library makes the functionality available to any
calling application.  

As you can see on line 317 of bsqldb.c, the application calls dbpivot()
immediately after dbresults().  dbpivot() collects and pivots the
results in memory.  When the last row has been fetched, it alters the
metadata: the original columns disappear, and in their place dbpivot()
creates the pivoted columns.  Processing then continues absolutely per
normal; dbnextrow() and all metadata functions operate on the pivoted
data just as they would on any other resultset.  

Very little of the dbpivot() code is specific to db-lib.  It could be
ported to the other libraries.  ISTM it would be good to port it to
ct-lib, thereby making it available to sqsh and DBD::Sybase.   If
you're interested in seeing that happen, though, you might want to put
it on your todo list, because it's not on mine!  

A few miscellaneous notes:

1.  Some formerly static functions are now exported.  That was
needed because most db-lib functions live in one module.  

2.  Documentation for the dbpivot() function consists of a few Doxygen
lines.  I'm undecided whether Doxygen is really how we want to
create/maintain documentation.   I generally rely on the vendors'
documentation, but as FreeTDS adds features the need for an independent
reference manual grows.  

3.  As an experiment, I added src/apps/bsqldb.1, a format and
location borrowed from NetBSD (and others).  Casting doc/bsqldb.txt
as a proper mdoc manpage took about an hour.  I am tempted to redo all
of doc/*.txt as mdoc files.  It would let us drop the txt2man utility,
thereby simplifying packaging and installation.  

--jkl