This shouldn't be a problem because the data that I list here are only a required amount for operators to perform, hence no calculations required. A list of 17 categories is maximum and I only wanted to list 3 months in advance.
-----Original Message----- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Friday, November 05, 2004 9:31 AM To: Scott Hamm Cc: 'Mysql ' (E-mail) Subject: Re: Column grouped by months You're kind of mixing display formatting with data retrieval. MySQL is a database, so it's display options for data are fairly limited, that's usually the job of the front end. But, if you still want to push it to format the way you requested, you need to do a join. Essentially, you're going select one months records and join it with another months records. SELECT category.category, goaldata.reqvalue, Nov.goalmonth as Nov, Dec.goalmonth as Dec FROM category LEFT JOIN goaldata as Nov ON category.id=Nov.catid AND Nov.goalmonth=11 LEFT JOIN goaldata as Dec ON category.id=Dec.catid AND Dec.goalmonth=12 As you can see, scaling this up to 12 months can get pretty long and may take a while depending on the size of your data. Normally, you wouldn't haven't any joins, but would sort by month. Your front end would then reformat the data from a vertical orientation to a horizontal, which would be quicker and scale better. On Nov 5, 2004, at 9:05 AM, Scott Hamm wrote: > My current database: > > mysql> SELECT > -> Category.Category, > -> GoalData.Reqvalue, > -> GoalData.GoalMonth > -> FROM > -> goaldata, > -> category > -> WHERE > -> goaldata.catid=category.id; > +-------------------------------------+----------+-----------+ > | Category | Reqvalue | GoalMonth | > +-------------------------------------+----------+-----------+ > | Mailroom Mail Opening Orders | 54 | 11 | > | Mailroom Rewards | 150 | 11 | > | Mailroom QC Opening Orders | 135 | 12 | > | Mailroom Opening Surveys | 200 | 11 | > | Mailroom QC Surveys | 350 | 11 | > | Mailroom Opening Resubmissions | 90 | 11 | > | Mailroom QC Resubmissions | 250 | 12 | > | Mailroom Microfilming | 700 | 11 | > | Mailroom Taping | 175 | 11 | > | Mailroom QC Taping | 350 | 11 | > | Mailroom Scanning | 1200 | 12 | > | Data Entry Orders (Key from paper) | 35 | 11 | > | Date Entry Surveys (Key form paper) | 45 | 11 | > | Data Entry Resubmissions | 50 | 11 | > | Data Entry Orders (Key from image) | 30 | 12 | > | Data Entry Surveys (Key from image) | 50 | 11 | > ... > > > I want my output to look like this: > > +-------------------------------------+----------+----------+ > | category | Nov | Dec | > +-------------------------------------+----------+----------+ > | Mailroom Mail Opening Orders | 54 | 54 | > | Mailroom Rewards | 150 | 150 | > | Mailroom QC Opening Orders | 135 | 135 | > | Mailroom Opening Surveys | 200 | 200 | > | Mailroom QC Surveys | 350 | 350 | > | Mailroom Opening Resubmissions | 90 | 90 | > | Mailroom QC Resubmissions | 250 | 250 | > | Mailroom Microfilming | 700 | 700 | > | Mailroom Taping | 175 | 175 | > | Mailroom QC Taping | 350 | 350 | > | Mailroom Scanning | 1200 | 1200 | > | Data Entry Orders (Key from paper) | 35 | 35 | > | Date Entry Surveys (Key form paper) | 45 | 45 | > | Data Entry Resubmissions | 50 | 50 | > | Data Entry Orders (Key from image) | 30 | 30 | > | Data Entry Surveys (Key from image) | 50 | 50 | > | Data Entry QC | 55 | 55 | > +-------------------------------------+----------+----------+ > > Something like this didn't work -- > SELECT > category.category, > goaldata.reqvalue, > goaldata.goalmonth=11 as Nov, > goaldata.goalmonth=12 as Dec > FROM > category, > goaldata > WHERE > goaldata.catid=goaldata.id; > > How can I get around to it? > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
