行转列

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'