Complicated Group SQL Query -

here query , result;

select  dateadd(dd, datediff(dd, 0, postdate), 0) date,  stores.name storename,  sum(poscash + poscredit + posbillcash + posbillcredit) revenue   storerevenue inner join stores on stores.id = storerevenue.storeid group dateadd(dd, datediff(dd, 0, postdate), 0), stores.name 

result : http://prntscr.com/zaele

i want create table (result) must group date , store names.

date, avcılar mağaza, ataşehir mağaza 2013-03-04, 150, 200 2013-03-05, 200, 250 2013-03-06, 300, 150 

rows of sub-group (these ones: 2013-03-04, 150, 200) date , incomes of each stores want kind of result have tried "pivot" in sql doesnt work me

sorry english. -thanks

you can use pivot function transform data rows columns:

select * (   select      dateadd(dd, datediff(dd, 0, postdate), 0) date,      stores.name storename,      (poscash + poscredit + posbillcash + posbillcredit) revenue     storerevenue   inner join stores      on stores.id = storerevenue.storeid ) d pivot (   sum(revenue)   storename in ([avcılar mağaza], [ataşehir mağaza]..) ) piv; 

or can use aggregate function case:

select dateadd(dd, datediff(dd, 0, postdate), 0) date,   sum(case when stores.name = 'avcılar mağaza'        poscash + poscredit + posbillcash + posbillcredit end) [avcılar mağaza],   sum(case when stores.name = 'ataşehir mağaza'        poscash + poscredit + posbillcash + posbillcredit end) [ataşehir mağaza] storerevenue inner join stores    on stores.id = storerevenue.storeid group dateadd(dd, datediff(dd, 0, postdate), 0) 

if have unknown number fo stores, can use dynamic sql:

declare @cols nvarchar(max),     @query  nvarchar(max)  select @cols = stuff((select distinct ',' + quotename(name)                      stores             xml path(''), type             ).value('.', 'nvarchar(max)')          ,1,1,'')  set @query = 'select date, ' + @cols + '                           (                 select                    dateadd(dd, datediff(dd, 0, postdate), 0) date,                    stores.name storename,                    (poscash + poscredit + posbillcash + posbillcredit) revenue                   storerevenue                 inner join stores                    on stores.id = storerevenue.storeid             ) x             pivot              (                 sum(revenue)                 storename in (' + @cols + ')             ) p '  execute(@query)