Re: matrix transpose in SQL?

The Oracle SQL Reference Guide has a chapter called "Turning a Table on its side". Akash, this is what you need to see.

I do not have the book on my side to copy the query. Could someone who has the book do it?

-Srini

Aakash Bordia <a_bordia_at_hotmail.com> wrote in message news:9ccvse$lp8$1_at_stlnews.stl.ibm.com...
> Well I dont think this exactly is slicing or dicing data. This is just a
> rearrangement of 2 dimensions. Anyways my interest lies in an SQL
 statement
> which can do this efficiently:)
> Thanks
> Aakash
>
> "Steve Long" <steven.long_at_erols.com> wrote in message
> news:9ccqm7$sld$1_at_bob.news.rcn.net...
> > 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 Thu May 03 2001 - 01:48:01 CEST