行转列的思考

最近遇到一个行转列的问题，给定一个时间段，统计每天每个车的签到情况，用动态拼接的方法实现，受Varchar(8000)的影响，拼接的SQL语句不能太长，遇到这种动态列头的问题，这种方法肯定不是很好，希望有高人指点新招~

/*建立车信息表*/

go
if NOT EXISTS (select 1 from sysobjects where id = object_id('Car') AND type='U')
BEGIN 
     CREATE TABLE Car 
    (
     Carid UNIQUEIDENTIFIER ,
     CarName VARCHAR(100)
     )
end
go
/*插入一些数据*/
INSERT INTO Car  SELECT NEWID(),'car1' UNION
                 SELECT NEWID(),'car2' UNION
                 SELECT NEWID(),'car3' UNION
                 SELECT NEWID(),'car4' UNION
                 SELECT NEWID(),'car5' UNION
                 SELECT NEWID(),'car6' UNION
                 SELECT NEWID(),'car7' 
/*建立签到表*/
go
if NOT EXISTS (select 1 from sysobjects where id = object_id('CarTime') AND type='U')
BEGIN 
     CREATE TABLE CarTime 
    (
     CarTimeID UNIQUEIDENTIFIER ,
     CarId UNIQUEIDENTIFIER, 
     operateDate DATETIME
     )
END
go
/*插入一些数据*/
DECLARE @i INT 
SET @i=1
WHILE (@i<=10)
BEGIN 
   INSERT INTO CarTime SELECT NEWID(),CarId,'2012-08-'+CONVERT(VARCHAR(10),@i) FROM Car
   SET @i=@i+1
end      
/*统计一段时间所有车每天的签到情况*/
go
if  EXISTS (select 1 from sysobjects where id = object_id('sp_CarTimeReport') AND type='P')
BEGIN 
   DROP PROC sp_CarTimeReport
END 
go       
CREATE PROC [dbo].[sp_CarTimeReport]  
 @BeginDate DATETIME,  
 @EndDate DATETIME 
AS   
BEGIN    
   DECLARE @begin DATETIME  
   DECLARE @end DATETIME  
   SET @begin=CONVERT(DATETIME,CONVERT(VARCHAR(10),@BeginDate,120))  
   SET @end=CONVERT(DATETIME,CONVERT(VARCHAR(10),@EndDate,120))  
     
   DECLARE @index DATETIME  
   SET @index=@begin  
   DECLARE @sql VARCHAR(8000)  
   SET @sql=' SELECT car.CarName as 车名'  
   WHILE(@index<=@end)  
   BEGIN  
  SET @sql=@sql+' ,case when sum(case when convert(varchar(10),carTime.OperateDate,120)='''+ CONVERT(VARCHAR(10),@index,120) +''' then 1 else null end) is not null then ''√'' else null end  as '''+ CONVERT(VARCHAR(10),@index,120) +'''  '  
  SET @index=@index+1  
   end  
  SET @sql=@sql+' FROM  Car left join CarTime on Car.carid=CarTime.carid 
         group by Car.carid,car.CarName   
         order by Car.CarName '           
   EXEC(@sql)  
END  

 

--DROP TABLE car
--DROP TABLE cartime
--DROP PROC  sp_CarTimeReport

--EXEC sp_CarTimeReport '2012-08-01','2012-08-30'

运行效果如下：