Re: matrix transpose in SQL?

Well,well....what I am talking about is data pivoting/inversion which comes before really loading the star schema. My step is a Transformation in the ETL sense, the result of which would be used to load the start schema. I dont know how you could invert data using a star schema. Can you help me out here?
Thanks
Aakash

"Steve Long" <steven.long_at_erols.com> wrote in message news:9ccjms$sn1$1_at_bob.news.rcn.net...
> 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 - 23:01:19 CEST