Re: matrix transpose in SQL?

interesting that your customers want to slice and dice data yet to be loaded.

"Aakash Bordia" <a_bordia_at_hotmail.com> wrote in message news:9ccmgo$i4q$1_at_stlnews.stl.ibm.com...
> 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 Sat Apr 28 2001 - 00:06:25 CEST