Re: matrix transpose in SQL?

> Why are you insisting that matrix must be modeled in RDBMS that way? There
> is a symmetry between rows and columns in matrix, and you loose it in your
> representation.

Yes, I agree: this table:

> 1 2 3
> 4 5 6
> 7 8 9

is _not_ a matrix. It is an _unordered_ set of rows of numbers. This:

> 1 2 3
> 7 8 9
> 4 5 6

is exactly the same table, and in fact the SQL standard does not guarantee that the order of rows you get from a query is always the same, not even when when no updates to the table happened (although in practice it usually does).

The cleanest way to represent matrices is of course:

I J VALUE
1 1 1
1 2 2
1 3 3
2 1 4
2 2 5
2 3 6
3 1 7
3 2 8
3 3 9

For your query, the problem is that with tables, you typically you don't know or care about how many rows there are, but the number of columns is fixed.

So any query that will flip rows and columns will by definition need to have

  SELECT X1, X2, .. Xn
  FROM
  WHERE with n equal to the number of rows in the original table (and something to order them by). The best thing I can think of is to have an SQL query that constructs, as a string, another SQL query that contains all the projections (i.e., the Xi's), which you then execute.

Another clever trick (as so often) can be found in Celko's SQL for Smarties (chapter 21.6, cross tabulations): use a cross tabulation table for the column that is to be pivoted. The cross tabulation table is an identity matrix (square, contains 1 on the diagonal and 0 elsewhere; again you have to know the number of columns in advance ...)

You then join your matrix table with the identity table while summing the product of the identity matrix with the quantity of interest.

This is typically used to 'flip' a 'sales report by year' so that each year is in a separate column, rather than a separate row. I find this practice debatable, because it's only a layout thing. Ah well,

                                                                      Philip

-- 
If you have a procedure with 10 parameters, you probably missed some. (Kraulis)
-----------------------------------------------------------------------------
Philip Lijnzaad, lijnzaad_at_ebi.ac.uk \ European Bioinformatics Institute,rm A2-08
+44 (0)1223 49 4639                 / Wellcome Trust Genome Campus, Hinxton
+44 (0)1223 49 4468 (fax)           \ Cambridgeshire CB10 1SD,  GREAT BRITAIN
PGP fingerprint: E1 03 BF 80 94 61 B6 FC  50 3D 1F 64 40 75 FB 53