示例表和数据
create table Product(Location char(2), Date_time datetime, Item_sold varchar(20))
insert Product select 'VA', '20101210 1:30:00 PM' ,'Candy'
insert Product select 'VA', '20101210 3:30:00 PM' ,'Chips'
insert Product select 'VA', '20101213 12:50:00 AM' ,'Wine'
insert Product select 'DC', '20101213 8:00:00 AM' ,'Gum'
insert Product select 'DC', '20101213 12:30:00 PM' ,'Bags'
insert Product select 'DC', '20101213 1:16:00 PM' ,'Cheese'
insert Product select 'DC', '20101213 12:00:00 AM' ,'Hotdog'
insert Product select 'NJ', '20101215 12:00:00 AM' ,'Coffee'
insert Product select 'NJ', '20101215 1:15:00 PM' ,'Beers'
insert Product select 'NJ', '20101215 3:45:00 AM' ,'Cream'
T-SQL 产生所需的结果
declare @start datetime
declare @end datetime
select @start = '20101208', @end = '20110105'
declare @sql nvarchar(max);
-- generate the column names
select @sql = coalesce(@sql + ',', '') + QuoteName(Convert(char(5),@start,101)+' - '+Convert(char(5),DT,101))
from (
select @start + number DT
from master..spt_values
where type='P' and number between 0 and DATEDIFF(D,@start,@end)) T;
-- replace the column names into the generic PIVOT form
set @sql = REPLACE('
;with COUNTS AS (
select p.location, Convert(char(5),@start,101)+'' - ''+Convert(char(5),@start + v.number,101) DT, X.C
from
(
Select distinct location From Product
where Date_time >= @start and Date_time < @end+1 -- * the date after, to handle the times
) p
inner join master..spt_values v on v.type=''P'' and v.number between 0 and DATEDIFF(D,@start,@end)
cross apply
(
select COUNT(*) C from product p2
where p2.Location=p.Location
and p2.date_time >= @start and p2.date_time < @start + v.number +1
) X
)
select location, :columns:
from COUNTS p
pivot (max(C) for DT in (:columns:)) pv',
':columns:', @sql)
-- execute for the results
exec sp_executesql @sql, N'@start datetime,@end datetime', @start, @end
