MySQL Lists are EOL. Please join:

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

I hope this gives you what you need:


SELECT
member.Member_Initials,
company.Company_Name,
subscription.Subscription_Expiry_Date
FROM
subscription
INNER JOIN member
ON
member.Member_URN = subscription.Member_URN
INNER JOIN company
ON
company.Company_URN = subscription.Company_URN


From member:

ABS
BV
CCS
DNV
GL
KR
LR
NK
RINA
RS
CRS
IRS


Many thanks

-- 
Aeon McNulty 


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

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