--> 测试数据:#TA if object_id('tempdb.dbo.#TA') is not null drop table #TA go create table #TA([id] int,[原因] varchar(8),[时间] datetime) insert #TA select 1,'材料不良','2013-11-11' union all select 2,'人为疏忽','2013-11-12' union all select 3,'材料不良','2013-11-13' union all select 4,'设计缺陷','2013-11-15' union all select 5,'其他','2013-11-16' union all select 6,'材料不良','2013-11-17' union all select 7,'人为疏忽','2013-11-17' union all select 8,'材料不良','2013-11-18' union all select 9,'设计缺陷','2013-11-19' union all select 10,'其他','2013-11-19' union all select 11,'材料不良','2013-12-11' union all select 12,'人为疏忽','2013-12-12' union all select 13,'材料不良','2013-12-13' union all select 14,'设计缺陷','2013-12-15' union all select 15,'其他','2013-12-16' union all select 16,'材料不良','2013-12-17' union all select 17,'人为疏忽','2013-12-17' union all select 18,'材料不良','2013-12-18' union all select 19,'设计缺陷','2013-12-19' union all select 20,'其他','2013-12-19' union all select 21,'材料不良','2014-01-11' union all select 22,'人为疏忽','2014-01-12' union all select 23,'材料不良','2014-01-13' union all select 24,'设计缺陷','2014-01-14' union all select 25,'其他','2014-01-15' union all select 26,'材料不良','2014-01-16' union all select 27,'人为疏忽','2014-01-17' union all select 28,'材料不良','2014-01-18' union all select 29,'设计缺陷','2014-01-19' union all select 30,'其他','2014-02-21' union all select 31,'材料不良','2014-03-11' union all select 32,'人为疏忽','2014-04-12' union all select 33,'材料不良','2014-05-13' union all select 39,'设计缺陷','2014-06-19' union all select 40,'其他','2014-06-21' --------------开始查询--------------------------select * from #TA ----------------结果---------------------------- /* */ DECLARE @bgn DATETIME , @end DATETIME , @SQL NVARCHAR(MAX) , @DateList NVARCHAR(MAX) SET @bgn = '2013-11-11' SET @end = '2014-06-21' SELECT @DateList = ISNULL(@DateList, '') + ',[' + CONVERT(VARCHAR(10), @bgn + number, 120) + ']' FROM master..spt_values WHERE type = 'p' AND number <= DATEDIFF(DAY, @bgn, @end) SET @DateList = STUFF(@DateList, 1, 1, '') PRINT @DateList SET @SQL = 'select *,' + REPLACE(@DateList, ',', '+') + ' AS 合计 from #TA pivot( count(ID) FOR 时间 in(' + @DateList + '))p' EXEC(@SQL)
--> 测试数据:#TA if object_id('tempdb.dbo.#TA') is not null drop table #TA go create table #TA([id] int,[原因] varchar(8),[时间] datetime) insert #TA select 1,'材料不良','2013-11-11' union all select 2,'人为疏忽','2013-11-12' union all select 3,'材料不良','2013-11-13' union all select 4,'设计缺陷','2013-11-15' union all select 5,'其他','2013-11-16' union all select 6,'材料不良','2013-11-17' union all select 7,'人为疏忽','2013-11-17' union all select 8,'材料不良','2013-11-18' union all select 9,'设计缺陷','2013-11-19' union all select 10,'其他','2013-11-19' union all select 11,'材料不良','2013-12-11' union all select 12,'人为疏忽','2013-12-12' union all select 13,'材料不良','2013-12-13' union all select 14,'设计缺陷','2013-12-15' union all select 15,'其他','2013-12-16' union all select 16,'材料不良','2013-12-17' union all select 17,'人为疏忽','2013-12-17' union all select 18,'材料不良','2013-12-18' union all select 19,'设计缺陷','2013-12-19' union all select 20,'其他','2013-12-19' union all select 21,'材料不良','2014-01-11' union all select 22,'人为疏忽','2014-01-12' union all select 23,'材料不良','2014-01-13' union all select 24,'设计缺陷','2014-01-14' union all select 25,'其他','2014-01-15' union all select 26,'材料不良','2014-01-16' union all select 27,'人为疏忽','2014-01-17' union all select 28,'材料不良','2014-01-18' union all select 29,'设计缺陷','2014-01-19' union all select 30,'其他','2014-02-21' union all select 31,'材料不良','2014-03-11' union all select 32,'人为疏忽','2014-04-12' union all select 33,'材料不良','2014-05-13' union all select 39,'设计缺陷','2014-06-19' union all select 40,'其他','2014-06-21' --------------开始查询--------------------------/* */ DECLARE @bgn DATETIME , @end DATETIME , @SQL NVARCHAR(MAX) , @DateList NVARCHAR(MAX) SET @bgn = '2013-11-11' SET @end = '2014-06-21' SELECT @DateList = ISNULL(@DateList, '') + ',[' + CONVERT(VARCHAR(7), DATEADD(MM,number,@bgn), 120) + ']' FROM master..spt_values WHERE type = 'p' AND number <= DATEDIFF(MM, @bgn, @end) SET @DateList = STUFF(@DateList, 1, 1, '') PRINT @DateList SET @SQL = ' ;WITH CTE AS ( SELECT [id],[原因],CONVERT(VARCHAR(7),时间,120)时间 FROM #TA ) select *,' + REPLACE(@DateList, ',', '+') + ' AS 合计 from CTE pivot( count(ID) FOR 时间 in(' + @DateList + '))p' EXEC(@SQL)
if object_id('tempdb.dbo.#TA') is not null drop table #TA
go
create table #TA([id] int,[原因] varchar(8),[时间] datetime)
insert #TA
select 1,'材料不良','2013-11-11' union all
select 2,'人为疏忽','2013-11-12' union all
select 3,'材料不良','2013-11-13' union all
select 4,'设计缺陷','2013-11-15' union all
select 5,'其他','2013-11-16' union all
select 6,'材料不良','2013-11-17' union all
select 7,'人为疏忽','2013-11-17' union all
select 8,'材料不良','2013-11-18' union all
select 9,'设计缺陷','2013-11-19' union all
select 10,'其他','2013-11-19' union all
select 11,'材料不良','2013-12-11' union all
select 12,'人为疏忽','2013-12-12' union all
select 13,'材料不良','2013-12-13' union all
select 14,'设计缺陷','2013-12-15' union all
select 15,'其他','2013-12-16' union all
select 16,'材料不良','2013-12-17' union all
select 17,'人为疏忽','2013-12-17' union all
select 18,'材料不良','2013-12-18' union all
select 19,'设计缺陷','2013-12-19' union all
select 20,'其他','2013-12-19' union all
select 21,'材料不良','2014-01-11' union all
select 22,'人为疏忽','2014-01-12' union all
select 23,'材料不良','2014-01-13' union all
select 24,'设计缺陷','2014-01-14' union all
select 25,'其他','2014-01-15' union all
select 26,'材料不良','2014-01-16' union all
select 27,'人为疏忽','2014-01-17' union all
select 28,'材料不良','2014-01-18' union all
select 29,'设计缺陷','2014-01-19' union all
select 30,'其他','2014-02-21' union all
select 31,'材料不良','2014-03-11' union all
select 32,'人为疏忽','2014-04-12' union all
select 33,'材料不良','2014-05-13' union all
select 39,'设计缺陷','2014-06-19' union all
select 40,'其他','2014-06-21'
--------------开始查询--------------------------select * from #TA
----------------结果----------------------------
/*
*/
DECLARE @bgn DATETIME ,
@end DATETIME ,
@SQL NVARCHAR(MAX) ,
@DateList NVARCHAR(MAX)
SET @bgn = '2013-11-11'
SET @end = '2014-06-21'
SELECT @DateList = ISNULL(@DateList, '') + ',[' + CONVERT(VARCHAR(10), @bgn
+ number, 120) + ']'
FROM master..spt_values
WHERE type = 'p'
AND number <= DATEDIFF(DAY, @bgn, @end)
SET @DateList = STUFF(@DateList, 1, 1, '')
PRINT @DateList
SET @SQL = 'select *,' + REPLACE(@DateList, ',', '+')
+ ' AS 合计 from #TA pivot( count(ID) FOR 时间 in(' + @DateList + '))p'
EXEC(@SQL)
if object_id('tempdb.dbo.#TA') is not null drop table #TA
go
create table #TA([id] int,[原因] varchar(8),[时间] datetime)
insert #TA
select 1,'材料不良','2013-11-11' union all
select 2,'人为疏忽','2013-11-12' union all
select 3,'材料不良','2013-11-13' union all
select 4,'设计缺陷','2013-11-15' union all
select 5,'其他','2013-11-16' union all
select 6,'材料不良','2013-11-17' union all
select 7,'人为疏忽','2013-11-17' union all
select 8,'材料不良','2013-11-18' union all
select 9,'设计缺陷','2013-11-19' union all
select 10,'其他','2013-11-19' union all
select 11,'材料不良','2013-12-11' union all
select 12,'人为疏忽','2013-12-12' union all
select 13,'材料不良','2013-12-13' union all
select 14,'设计缺陷','2013-12-15' union all
select 15,'其他','2013-12-16' union all
select 16,'材料不良','2013-12-17' union all
select 17,'人为疏忽','2013-12-17' union all
select 18,'材料不良','2013-12-18' union all
select 19,'设计缺陷','2013-12-19' union all
select 20,'其他','2013-12-19' union all
select 21,'材料不良','2014-01-11' union all
select 22,'人为疏忽','2014-01-12' union all
select 23,'材料不良','2014-01-13' union all
select 24,'设计缺陷','2014-01-14' union all
select 25,'其他','2014-01-15' union all
select 26,'材料不良','2014-01-16' union all
select 27,'人为疏忽','2014-01-17' union all
select 28,'材料不良','2014-01-18' union all
select 29,'设计缺陷','2014-01-19' union all
select 30,'其他','2014-02-21' union all
select 31,'材料不良','2014-03-11' union all
select 32,'人为疏忽','2014-04-12' union all
select 33,'材料不良','2014-05-13' union all
select 39,'设计缺陷','2014-06-19' union all
select 40,'其他','2014-06-21'
--------------开始查询--------------------------/*
*/
DECLARE @bgn DATETIME ,
@end DATETIME ,
@SQL NVARCHAR(MAX) ,
@DateList NVARCHAR(MAX)
SET @bgn = '2013-11-11'
SET @end = '2014-06-21'
SELECT @DateList = ISNULL(@DateList, '') + ',[' + CONVERT(VARCHAR(7), DATEADD(MM,number,@bgn), 120) + ']'
FROM master..spt_values
WHERE type = 'p'
AND number <= DATEDIFF(MM, @bgn, @end)
SET @DateList = STUFF(@DateList, 1, 1, '')
PRINT @DateList
SET @SQL = '
;WITH CTE AS
(
SELECT [id],[原因],CONVERT(VARCHAR(7),时间,120)时间
FROM #TA
)
select *,' + REPLACE(@DateList, ',', '+')
+ ' AS 合计 from CTE pivot( count(ID) FOR 时间 in(' + @DateList + '))p'
EXEC(@SQL)