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>
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 IAakash> 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-08Received on Tue May 01 2001 - 12:14:38 CEST
+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