Ох, мне тут в наследство достался запрос на 2к строк.

Что-то не могу сооброзить как его переписать

весь запрос состоит из примерно сотни вот таких подзапросов
select 85 as 'store_id', (select name from core_store where store_id=85) as 'Store',
(select count(1) from sales_flat_order where status IN ('reserved', 'complete', 'collect') and date(created_at)=curdate()-INTERVAL 0 DAY AND store_id=85) as '-0'
,
(select count(1) from sales_flat_order where status IN ('reserved', 'complete', 'collect') and date(created_at)=curdate()-INTERVAL 1 DAY AND store_id=85) as '-1'
,
(select count(1) from sales_flat_order where status IN ('reserved', 'complete', 'collect') and date(created_at)=curdate()-INTERVAL 2 DAY AND store_id=85) as '-2'
,
(select count(1) from sales_flat_order where status IN ('reserved', 'complete', 'collect') and date(created_at)=curdate()-INTERVAL 3 DAY AND store_id=85) as '-3'
,
(select count(1) from sales_flat_order where status IN ('reserved', 'complete', 'collect') and date(created_at)=curdate()-INTERVAL 4 DAY AND store_id=85) as '-4'
,
(select count(1) from sales_flat_order where status IN ('reserved', 'complete', 'collect') and date(created_at)=curdate()-INTERVAL 5 DAY AND store_id=85) as '-5'
,
(select count(1) from sales_flat_order where status IN ('reserved', 'complete', 'collect') and date(created_at)=curdate()-INTERVAL 6 DAY AND store_id=85) as '-6'
,
(select count(1) from sales_flat_order where status IN ('reserved', 'complete', 'collect') and date(created_at)=curdate()-INTERVAL 7 DAY AND store_id=85) as '-7'
,
(select count(1) from sales_flat_order where status IN ('reserved', 'complete', 'collect') and date(created_at)=curdate()-INTERVAL 8 DAY AND store_id=85) as '-8'
,
(select count(1) from sales_flat_order where status IN ('reserved', 'complete', 'collect') and date(created_at)=curdate()-INTERVAL 9 DAY AND store_id=85) as '-9'
,
(select count(1) from sales_flat_order where status IN ('reserved', 'complete', 'collect') and date(created_at)=curdate()-INTERVAL 10 DAY AND store_id=85) as '-10'
,
(select count(1) from sales_flat_order where status IN ('reserved', 'complete', 'collect') and date(created_at)=curdate()-INTERVAL 11 DAY AND store_id=85) as '-11'
,
(select count(1) from sales_flat_order where status IN ('reserved', 'complete', 'collect') and date(created_at)=curdate()-INTERVAL 12 DAY AND store_id=85) as '-12'
,
(select count(1) from sales_flat_order where status IN ('reserved', 'complete', 'collect') and date(created_at)=curdate()-INTERVAL 13 DAY AND store_id=85) as '-13'

Вопрос вообщем в следующем, как можно избавится от этого?

Это весь запрос? А чем не устраивает?

Я не вижу весь запрос. Видимо, тут нужен PIVOT, если СУБД поддерживает его, можно переписать. Если нет, можно PIVOTING делать НА КЛИЕНТЕ, соотв. на сервере можно размернуть столбцы в строки.