select distinct item.reportoid, item.lineoid, item.accountoid, sun.amount, mon.amount, tue.amount, wes.amount, thur.amount, fri.amount, sat.amount
from expenseitem item
left join (select reportoid, accountoid, lineoid, amount, itemDate from expenseitem
where itemDate = '2007-11-04' ) sun
on item.reportoid = sun.reportoid and
item.accountoid = sun.accountoid and
item.lineoid = sun.lineoid and
item.itemDate = sun.itemDate
left join (select reportoid, accountoid, lineoid, amount, itemDate from expenseitem
where itemDate = adddate('2007-11-04', 1) ) mon
on item.reportoid = mon.reportoid and
item.accountoid = mon.accountoid and
item.lineoid = mon.lineoid and
item.itemDate = mon.itemDate
left join (select reportoid, accountoid, lineoid,amount, itemDate from expenseitem
where itemDate = adddate('2007-11-04', 2)) tue
on item.reportoid = tue.reportoid and
item.accountoid = tue.accountoid and
item.lineoid = tue.lineoid and
item.itemDate = tue.itemDate
left join (select reportoid, accountoid, lineoid,amount, itemDate from expenseitem
where itemDate = adddate('2007-11-04', 3) ) wes
on item.reportoid = wes.reportoid and
item.accountoid = wes.accountoid and
item.lineoid = wes.lineoid and
item.itemDate = wes.itemDate
left join (select reportoid, accountoid, lineoid,amount, itemDate from expenseitem
where itemDate = adddate('2007-11-04', 4) ) thur
on item.reportoid = thur.reportoid and
item.accountoid = thur.accountoid and
item.lineoid = thur.lineoid and
item.itemDate = thur.itemDate
left join (select reportoid, accountoid, lineoid,amount, itemDate from expenseitem
where itemDate = adddate('2007-11-04', 5) ) fri
on item.reportoid = fri.reportoid and
item.accountoid = fri.accountoid and
item.lineoid = fri.lineoid and
item.itemDate = fri.itemDate
left join (select reportoid, accountoid, lineoid,amount, itemDate from expenseitem
where itemDate = adddate('2007-11-04', 6) ) sat
on item.reportoid = sat.reportoid and
item.accountoid = sat.accountoid and
item.lineoid = sat.lineoid and
item.itemDate = sat.itemDate
where item.reportoid = 3712
order by reportoid, accountoid;