That's just perfect! Than you so much!
-- Aeon McNulty On 23/7/04 8:50 pm, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> 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 <[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 >>>> >>>> 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]
