MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Aeon McNulty Date:July 23 2004 6:12pm
Subject:Re: Returning Column and Row Headings Like a Spreadsheet
View as plain text  
Hi Shawn

Yes, that's pretty much it.  I'm impressed that you managed to work that out
from my previous email which was a bit thin on detail!

> 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.

The number of columns are variable but they don't vary often so I can do a
quick query first to give me the number and then use that number to specify
the number of columns in the second query. Does that make sense?

Please see my other post. Does that give you enough information?

Many thanks

-- 
Aeon McNulty 


On 23/7/04 6:36 pm, "SGreen@stripped" <SGreen@stripped> wrote:

> 
> 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


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