Re: matrix transpose in SQL?

it seems to me a star schema may solve your problem...such situations is precisely why the star schema was conceived.

"Aakash Bordia" <a_bordia_at_hotmail.com> wrote in message news:9cc87u$i3a$1_at_stlnews.stl.ibm.com...
> 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 - 22:07:19 CEST