Complex? MySQL Query

I have a table with the columns, id, date, estValue & gradeid. Each grade id has about 12 records and there are approximately 10 different grades for a total of about 120 records [give or take] I need to create a selection from the database that gives me a result set that looks something like this:

date    |gradeid1 |gradeid2 |gradeid3 3|etc...
01/01/01|estValue1|estValue2||estValue3|etc开发者_Python百科....
01/01/02|estValue1|estValue2||estValue3|etc....

I have a query that can select ONE record, but I need them all ordered by date:

select eh.id, eh.date as wdate,
    (select estValue from nas_estimatehistory where `date` like '2011-03-%' and gradeid = '1') as '1',
    (select estValue from nas_estimatehistory where `date` like '2011-03-%' and gradeid = '2') as '2',
    (select estValue from nas_estimatehistory where `date` like '2011-03-%' and gradeid = '3') as '3',
    (select estValue from nas_estimatehistory where `date` like '2011-03-%' and gradeid = '4') as '4',
    (select estValue from nas_estimatehistory where `date` like '2011-03-%' and gradeid = '5') as '5',
    (select estValue from nas_estimatehistory where `date` like '2011-03-%' and gradeid = '6') as '6',
    (select estValue from nas_estimatehistory where `date` like '2011-03-%' and gradeid = '7') as '7',
    (select estValue from nas_estimatehistory where `date` like '2011-03-%' and gradeid = '8') as '8',
    (select estValue from nas_estimatehistory where `date` like '2011-03-%' and gradeid = '9') as '9',
    (select estValue from nas_estimatehistory where `date` like '2011-03-%' and gradeid = '10') as '10'
from nas_estimatehistory eh  
group by wdate 
order by `wdate` asc
limit 1;

which returns pretty much what I need, but only 1 row, if I remove the limit, then I get a row for each month [12 rows] but all the column values are the same [they should all be different] i.e. estValue in each row AND column should be a unique value...

I'm not sure what the best way to go about this is.

-thanks -sean

---

Cross-tabulation is the key: use an aggregate function and the IF function.

SELECT eh.date AS wdate,
    GROUP_CONCAT(IF(gradeid=1,estValue,NULL)) as `1`,
    GROUP_CONCAT(IF(gradeid=2,estValue,NULL)) as `2`,
    GROUP_CONCAT(IF(gradeid=3,estValue,NULL)) as `3`,
    GROUP_CONCAT(IF(gradeid=4,estValue,NULL)) as `4`,
    GROUP_CONCAT(IF(gradeid=5,estValue,NULL)) as `5`,
    GROUP_CONCAT(IF(gradeid=6,estValue,NULL)) as `6`,
    GROUP_CONCAT(IF(gradeid=7,estValue,NULL)) as `7`,
    GROUP_CONCAT(IF(gradeid=8,estValue,NULL)) as `8`,
    GROUP_CONCAT(IF(gradeid=9,estValue,NULL)) as `9`,
    GROUP_CONCAT(IF(gradeid=10,estValue,NULL)) as `10`
FROM nas_estimatehistory eh  
GROUP BY wdate 
ORDER BY `wdate` ASC;

MAX or MIN may also be suitable aggregate functions.

---

select eh.id, wdate,
    (select estValue from nas_estimatehistory where `date` like `pattern` and gradeid = '1') as '1',
    (select estValue from nas_estimatehistory where `date` like `pattern` and gradeid = '2') as '2',
    (select estValue from nas_estimatehistory where `date` like `pattern` and gradeid = '3') as '3',
    (select estValue from nas_estimatehistory where `date` like `pattern` and gradeid = '4') as '4',
    (select estValue from nas_estimatehistory where `date` like `pattern` and gradeid = '5') as '5',
    (select estValue from nas_estimatehistory where `date` like `pattern` and gradeid = '6') as '6',
    (select estValue from nas_estimatehistory where `date` like `pattern` and gradeid = '7') as '7',
    (select estValue from nas_estimatehistory where `date` like `pattern` and gradeid = '8') as '8',
    (select estValue from nas_estimatehistory where `date` like `pattern` and gradeid = '9') as '9',
    (select estValue from nas_estimatehistory where `date` like `pattern` and gradeid = '10') as '10'
from (
    select eh.*, date_format(eh.date, '%Y-%m') wdate,
                 concat(date_format(eh.date, '%Y-%m'),'-%') `pattern`
    from nas_estimatehistory) eh
group by wdate
order by wdate asc;

---

Like I said, forget the group part. It has no effect. I think I see your problem.

Replace gradeid = '1' with gradeid = eh.gradeid