Pivot In Values with dates?

A开发者_StackOverflow中文版t the end of my sql, I am using the following code. Is there any way of replacing the fixed strings [2011/07/14], [2011/07/16], etc, to GetDate() value?

PIVOT 
    (
       count([AppointmentsBooked])
       FOR [date] IN ([2011/07/14], [2011/07/16], [2011/07/17],[2011/07/18],[2011/07/21])
    ) as pivottable

Can you try to use BETWEEN and DATEADD in following manner:

DECLARE @dates TABLE(value DateTime)

INSERT INTO @dates VALUES
(GETDATE()),
('2011/07/9'),
('2011/07/17'), 
('2011/07/18'), 
('2011/07/21')

SELECT value FROM @dates
WHERE value BETWEEN GETDATE() AND DATEADD(day, 5, GETDATE())

You can create a string of all dates which is comma seperated with '[' and ']' before and after each date. assign this string to a string variable (@dates) and use the string spit method to split all dates inside the pivot query.

this question was posted about a year ago. i don't care. i have some code that might be exactly what the OP wanted.... i'm sure he'll never come back and chose an answer but still.... all i want to do is count the records by month with a pivot for a few tables and ultimately compare the number of records for each month for each table. however... in this code there is only one table (rt_taco_15m) but that doesn't matter. i just haven't written the rest to completely fit my needs. but i think it fits the needs of the OP or at least gets him on a good start.... if he truly has been waiting a year on this problem. lol.

if object_id('tempdb..#temp') is not null drop table #temp
if object_id('tempdb..#temp2') is not null drop table #temp2
if object_id('tempdb..#temp3') is not null drop table #temp3

declare @start_date as datetime
    set @start_date = cast('1-1-2012' as datetime)
declare @end_date as datetime
    set @end_date = cast('9-1-2012' as datetime)


;with cte as    (
    select  @start_date as [start], 
            dateadd(month, 1, @start_date) as [end]

    union   all

    select  dateadd(month, 1, [start]) as [start], 
            dateadd(month, 1, dateadd(month, 1, [start])) as [end]
    from    cte
    where   dateadd(month, 1, [start]) <= @end_date
)


(select 'rt_taco_15m' as table_name, 
        convert(varchar(10), [start], 101) as [start],  
        convert(varchar(10), [end], 101) as [end],
        datename(month, [start]) as month_name, 
        cast([start] as integer) as orderby,
        count(taco.taco_record_id) as [range_count] 

into    #temp 

from    cte

        left outer join rt_taco_15m as taco
            on taco.period >= cte.[start] and 
                taco.period < cte.[end]  

group   by cte.[start], cte.[end])


select  table_name as table_name, 
        convert(varchar(10), getdate(), 101)  as [start], 
        convert(varchar(10), getdate(), 101) as [end],
        'Total' as month_name, 
        cast(dateadd(month,2,@end_date) as integer) as orderby,
        range_sum as [range_count]

into    #temp2

from    (select table_name, sum([range_count]) as range_sum
            from #temp group by table_name) as summed_up

select  * 
into    #temp3
from    (select * from #temp
         union all
         select * from #temp2) as x
order by orderby


select * from #temp3

declare @cols nvarchar(2000)
select  @cols = stuff(
                    (select '],[' + month_name
                     from    #temp3 
                     order by orderby
                     for xml path('') ) 
                , 1, 2, '') + ']'
print @cols


if object_id('tempdb..#temp2') is not null drop table #temp2
declare @query varchar(max)
set @query = N'
                select  table_name, ' + @cols + N'

                from    (select table_name, month_name, range_count
                            from #temp3) p 

                        pivot ( sum(range_count) for month_name in ( '+ @cols +' ) ) as pvt'

execute(@query