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
