List:General Discussion« Previous MessageNext Message »
From:Aeon McNulty Date:July 23 2004 8:13pm
Subject:Re: Returning Column and Row Headings Like a Spreadsheet
View as plain text  
That's just perfect!

Than you so much!

-- 
Aeon McNulty 


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

> 
> Yep, just what I needed.
> 
> SELECT 
>         c.CompanyName
>         , MAX(IF(m.Member_Initials='ABS', s.Subscription_Expiry_Date,
> NULL))as 'ABS' 
>         , MAX(IF(m.Member_Initials='BV', s.Subscription_Expiry_Date,
> NULL))as 'BV' 
>         , MAX(IF(m.Member_Initials='CCS', s.Subscription_Expiry_Date,
> NULL)) as 'CCS' 
>         , MAX(IF(m.Member_Initials='DNV', s.Subscription_Expiry_Date,
> NULL)) as 'DNV' 
>         , MAX(IF(m.Member_Initials='GL', s.Subscription_Expiry_Date,
> NULL)) as 'GL' 
>         , MAX(IF(m.Member_Initials='KR', s.Subscription_Expiry_Date,
> NULL)) as 'KR' 
>         , MAX(IF(m.Member_Initials='LR', s.Subscription_Expiry_Date,
> NULL)) as 'LR' 
>         , MAX(IF(m.Member_Initials='NK', s.Subscription_Expiry_Date,
> NULL)) as 'NK' 
>         , MAX(IF(m.Member_Initials='RINA', s.Subscription_Expiry_Date,
> NULL)) as 'RINA' 
>         , MAX(IF(m.Member_Initials='RS', s.Subscription_Expiry_Date,
> NULL)) as 'RS' 
>         , MAX(IF(m.Member_Initials='CRS', s.Subscription_Expiry_Date,
> NULL)) as 'CRS' 
>         , MAX(IF(m.Member_Initials='IRS', s.Subscription_Expiry_Date,
> NULL)) as 'IRS' 
> FROM subscription s
> INNER JOIN member m
> ON m.Member_URN = s.Member_URN
> INNER JOIN company c
> ON c.Company_URN = s.Company_URN
> GROUP BY c.CompanyName
> 
> Now do you see why I needed you to send me the column names? It is a
> simple pattern but requires a little advance knowledge of the data. It
> should be quite easy for you to script a query shaped like this in order
> to return just the columns you get from any generic query.
> 
> The reason everything lines up by rows is because of the GROUP BY
> statement. Because you gave me a date column to work with I was limited
> in my choice of aggregating function. You can replace the MAX() in the
> above query from any other function in this list (so long as it is
> compatible with the data you need to aggregate) :
> http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html
> 
> This works because for each column, the aggregate function either sees a
> value or a NULL. That's what the IF() is doing, picking what is MAX()-ed
> and what isn't based on the value of Member_Initials.
> 
> Have fun playing around with it!
> 
> Yours, 
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
> 
> 
> 
> Aeon McNulty <aeon@stripped> wrote on 07/23/2004 03:19:41
> PM:
> 
>> 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
>> 
>> 
>> 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