MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:SGreen Date:July 23 2004 5:36pm
Subject:Re: <no subject>
View as plain text  
If I understand you correctly Basically you want to convert this query 
output:

+-------+-------+-------+
|A.value|B.value|C.value|
+-------+-------+-------+
|  a1   |  b1   |   c1  |
|  a1   |  b1   |   c2  |
|  a2   |  b1   |   c3  |
|  a2   |  b1   |   c4  |
|  a1   |  b2   |   c5  |
|  a1   |  b2   |   c6  |
|  a2   |  b2   |   c7  |
|  a2   |  b2   |   c8  |
|  a1   |  b3   |   c9  |
| ...   | ...   |  ...  |
| a(j)  | b(k)  |  c(n) |
+-------+-------+-------+
into something like:
+-------+----------+----------+----------+-----------+
|       |    a1    |     a2   |    ...   |   a(j)    |
+-------+----------+----------+----------+-----------+
|  b1   | f(c1,c2) | f(c3,c4) |    ...   |    ...    |
|  b2   | f(c5,c6) | f(c7,c8) |    ...   |    ...    |
|  b3   |f(c9,...) |   ...    |    ...   |    ...    |
| ...   |   ...    |   ...    |    ...   |    ...    |
|  b(k) | f(c1,c2) | f(c2,c3) |    ...   |f(...,C(n))|
+-------+----------+----------+----------+-----------+

Where f() represents one of the aggregate functions: SUM, AVG, STD, MIN, 
MAX, etc.

Bad news: MySQL does not have an SQL-only solution for the general case 
(when you do not know the number of columns)
Good news: it is usually quite simple to write a script to handle the 
general case.
Good news: For a specific case (when you know the number of columns) the 
query you seek to write follows a simple and predictable pattern and is 
not hard to write at all.

If you would like help in writing a specific pivot table (cross-tab) 
query. Please post your tables' structures (I prefer the output of SHOW 
CREATE TABLE xxx)  and tell us how you want your cross-tab report setup 
(column headers from where, row headers from where, and a formula to use 
for your cells)

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Aeon McNulty <aeon@stripped> wrote on 07/23/2004 12:30:21 
PM:

> Hi, I hope someone on the list can help me.
> 
> Is there an easy and straightforward way way of displaying the data from
> three tables in a spreadsheet like format using MySQL 4.0?
> 
> Table A has the column headings
> Table B has the row headings
> Table C is a line items file than cross relates the two
> 
> Many thanks
> 
> -- 
> Aeon McNulty 
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 

Thread
<no subject>Aeon McNulty23 Jul
  • Re: <no subject>SGreen23 Jul
    • Re: Returning Column and Row Headings Like a SpreadsheetAeon McNulty23 Jul
      • Re: Returning Column and Row Headings Like a SpreadsheetSGreen23 Jul
        • Re: Returning Column and Row Headings Like a SpreadsheetAeon McNulty23 Jul
          • Re: Returning Column and Row Headings Like a SpreadsheetSGreen23 Jul
            • Re: Returning Column and Row Headings Like a SpreadsheetAeon McNulty23 Jul