摘要:老生常谈，SQL行列转换的话题
declare @table1 table (Date date)
insert @table1 values('2011-1-1')
insert @table1 values('2011-2-1')
insert @table1 values('2011-3-1')
insert @table1 values('2011-4-1')
insert @table1 values('2011-5-1')
insert @table1 values('2011-6-1')
declare @table2 table( item varchar(2), starttime date, Endtime date)
insert @table2 values('A','2011-2-1','2011-3-16')
insert @table2 values('B','2011-1-1','2011-4-20')
insert @table2 values('C','2011-3-10','2011-6-1')
insert @table2 values('D','2011-4-15','2011-5-1')
SELECT item,*
FROM
(SELECT item ,Date, starttime,endtime
FROM @table2 join @table1 on date between starttime and Endtime) AS SourceTable
PIVOT
(
count(Date)
FOR Date IN ([2011-1-1], [2011-2-1], [2011-3-1], [2011-4-1], [2011-5-1],[2011-6-1])
) AS PivotTable;
执行结果如下：
