MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:SGreen Date:July 23 2004 6:35pm
Subject:Re: Returning Column and Row Headings Like a Spreadsheet
View as plain text  
 I will be more than happy to help you build a case-specific crosstab 
report. I have almost enough information. Could you post a 3 column-query 
that will actually produce from your data the information you would like 
to have reformatted? 

Column one needs to be your column names, column two needs to be your row 
headers, and the 3rd column needs to have the data you want to see merged 
into your cells (I won't need the data only the query). It should look 
something like:

SELECT a.name, b.name, c.data
FROM c
INNER JOIN a
        on a.id = c.a_id
INNER JOIN b
        on b.id = c.b_id
WHERE ..... (any condition will do)

I will also need a list of the potential column headers (this time I do 
need the data):

SELECT DISTINCT name
FROM a

With that I will have enough information to "pivot" your query into a 
crosstab report. Hopefully you will be able to spot the pattern and be 
able to adjust it to fit your other situations as well. 

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



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

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