Re: matrix transpose in SQL?

The reason for my representation of numbers as I had in my original mail, is that it is not uncommon amongst data warehousing customers to have data laid out in such format which they either want to pivot or transpose. My example was probably the simplest of all that I could have provided.A practical case might be a matrix of monthXproduct, with sales being the numbers, and the end user wants it the other way.
Preety normal requirement. Also they would have an order on such a table. I agree that RDBMS do not provide an order inherently. Thanks for the reference to Celkos guide. I would surely look at the other kind of example that you provide.
My previous posted solution numbers all the elements of the matrix and goes from there.
Thanks
Aakash

> 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
Received on Fri Apr 27 2001 - 18:57:39 CEST