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 <[EMAIL PROTECTED]> 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, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED]
>
