行转列的思考
<div id="cnblogs_post_body">最近遇到一个行转列的问题，给定一个时间段，统计每天每个车的签到情况，用动态拼接的方法实现，受Varchar(8000)的影响，拼接的SQL语句不能太长，遇到这种动态列头的问题，这种方法肯定不是很好，希望有高人指点新招~/*建立车信息表*/
<div class="cnblogs_code">goif NOT EXISTS (select 1 from sysobjects where id = object_id('Car') AND type='U')BEGIN CREATE TABLE Car ( Carid UNIQUEIDENTIFIER , CarName VARCHAR(100) )endgo/*插入一些数据*/INSERT INTO CarSELECT 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' /*建立签到表*/goif NOT EXISTS (select 1 from sysobjects where id = object_id('CarTime') AND type='U')BEGIN CREATE TABLE CarTime ( CarTimeID UNIQUEIDENTIFIER , CarId UNIQUEIDENTIFIER, operateDate DATETIME )ENDgo/*插入一些数据*/DECLARE @i INT SET @i=1WHILE (@i<=10)BEGIN INSERT INTO CarTime SELECT NEWID(),CarId,'2012-08-'+CONVERT(VARCHAR(10),@i) FROM Car SET @i=@i+1end /*统计一段时间所有车每天的签到情况*/goifEXISTS (select 1 from sysobjects where id = object_id('sp_CarTimeReport') AND type='P')BEGIN DROP PROC sp_CarTimeReportEND 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 ''&radic;'' else null endas '''+ CONVERT(VARCHAR(10),@index,120) +'''' SET @index=@index+1 end SET @sql=@sql+' FROMCar 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 PROCsp_CarTimeReport--EXEC sp_CarTimeReport '2012-08-01','2012-08-30'
页:
[1]