MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:SGreen Date:July 23 2004 7:50pm
Subject:Re: Returning Column and Row Headings Like a Spreadsheet
View as plain text  
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
> 
> -- 
> 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
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 

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