Re: matrix transpose in SQL?

From: Philip Lijnzaad <lijnzaad_at_ebi.ac.uk>
Date: 01 May 2001 11:14:38 +0100
Message-ID: <u7itjlv0j5.fsf_at_sol6.ebi.ac.uk>


Aakash> Makes sense. The kind of situation I was talking about usually occurs in
Aakash> importing data from a spreadsheet:
Aakash> example:

Aakash> MONTH    PRODUCT    SALES    PRODUCT SALES
Aakash> JAN            COKE            100        PEPSI         100
Aakash> FEB            COKE             300        PEPSI        200

Aakash> The output needed is:
Aakash> PRODUCT    JAN    FEB
Aakash> COKE          100       300
Aakash> PEPSI           100       200

Aakash> Look at the number columns here, they get transposed.  That is what I
Aakash> wanted without an intermediate representation (in physical form, an Aakash> in-memory form is acceptable).

This is very close to the cross tabulation example given in Celko's "SQL for Smarties".

However, I maintain that I don't see much need for cross-tabulation here; insert this stuff into a plain Sales(product_id, year?, month, amount) table, write a little routine that does the layout of this in the way you want; this should be trivial (for say, <= 10000 cells; if bigger, I doubt it'd be meaningfull to print this out). Cheers,

                                                                       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 Tue May 01 2001 - 12:14:38 CEST

Original text of this message