27,579
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :SQL77(只为思齐老)
-- Date :2010-03-21 13:51:45
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([类型] varchar(4),[时间] datetime,[姓名] varchar(6),[C4] varchar(5))
insert #TB
select '通过','2008-12-12 20:58','班才荣','C0035' union all
select '通过','2008-12-13 7:30','班才荣','C0035' union all
select '通过','2008-12-13 20:54','班才荣','C0035' union all
select '通过','2008-12-14 5:00','班才荣','C0035' union all
select '通过','2008-12-16 8:02','班才荣','C0035' union all
select '通过','2008-12-16 20:56','班才荣','C0035' union all
select '通过','2008-12-17 8:07','班才荣','C0035' union all
select '通过','2009-6-1 14:34','卜胜强','C0497' union all
select '通过','2009-6-1 14:34','卜胜强','C0497' union all
select '通过','2009-6-1 14:34','卜胜强','C0497' union all
select '通过','2009-6-1 14:12','卜胜强','C0497' union all
select '通过','2009-6-13 18:37','蔡梅','C0384' union all
select '通过','2009-6-13 17:32','蔡梅','C0384' union all
select '通过','2009-6-13 17:55','蔡梅','C0384' union all
select '通过','2009-6-13 13:23','蔡梅','C0384' union all
select '通过','2009-6-13 12:03','蔡梅','C0384' union all
select '通过','2009-6-13 7:52','蔡梅','C0384'
--------------开始查询--------------------------
select *,ID=IDENTITY(INT,1,1) INTO #T from #TB
DECLARE @S VARCHAR(8000)
SELECT @S=ISNULL(@S+',','')+'MAX(CASE WHEN NUM='+LTRIM(NUM)+' THEN CONVERT(VARCHAR(20),时间,120) ELSE '''' END)AS ''时间'+LTRIM(NUM)+''''
FROM (SELECT DISTINCT NUM FROM
(SELECT *,(SELECT COUNT(1) FROM #T WHERE 姓名=T.姓名 AND C4=T.C4 AND ID<=T.ID) AS NUM FROM #T T
)AS T
)T
EXEC('SELECT 姓名,C4,'+ @S+' FROM (SELECT *,(SELECT COUNT(1) FROM #T WHERE 姓名=T.姓名 AND C4=T.C4 AND ID<=T.ID) AS NUM FROM #T T) AS T GROUP BY 姓名,C4 ')
----------------结果----------------------------
/*
姓名 C4 时间1 时间2 时间3 时间4 时间5 时间6 时间7
------ ----- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
班才荣 C0035 2008-12-12 20:58:00 2008-12-13 07:30:00 2008-12-13 20:54:00 2008-12-14 05:00:00 2008-12-16 08:02:00 2008-12-16 20:56:00 2008-12-17 08:07:00
蔡梅 C0384 2009-06-13 18:37:00 2009-06-13 17:32:00 2009-06-13 17:55:00 2009-06-13 13:23:00 2009-06-13 12:03:00 2009-06-13 07:52:00
卜胜强 C0497 2009-06-01 14:34:00 2009-06-01 14:34:00 2009-06-01 14:34:00 2009-06-01 14:12:00
*/
----------------------------------------------------------------
-- Author :SQL77(只为思齐老)
-- Date :2010-03-21 13:51:45
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([类型] varchar(4),[时间] datetime,[姓名] varchar(6),[C4] varchar(5))
insert #TB
select '通过','2008-12-12 20:58','班才荣','C0035' union all
select '通过','2008-12-13 7:30','班才荣','C0035' union all
select '通过','2008-12-13 20:54','班才荣','C0035' union all
select '通过','2008-12-14 5:00','班才荣','C0035' union all
select '通过','2008-12-16 8:02','班才荣','C0035' union all
select '通过','2008-12-16 20:56','班才荣','C0035' union all
select '通过','2008-12-17 8:07','班才荣','C0035' union all
select '通过','2009-6-1 14:34','卜胜强','C0497' union all
select '通过','2009-6-1 14:34','卜胜强','C0497' union all
select '通过','2009-6-1 14:34','卜胜强','C0497' union all
select '通过','2009-6-1 14:12','卜胜强','C0497' union all
select '通过','2009-6-13 18:37','蔡梅','C0384' union all
select '通过','2009-6-13 17:32','蔡梅','C0384' union all
select '通过','2009-6-13 17:55','蔡梅','C0384' union all
select '通过','2009-6-13 13:23','蔡梅','C0384' union all
select '通过','2009-6-13 12:03','蔡梅','C0384' union all
select '通过','2009-6-13 7:52','蔡梅','C0384'
--------------开始查询--------------------------
select *,ID=IDENTITY(INT,1,1) INTO #T from #TB
DECLARE @S VARCHAR(8000)
SELECT @S=ISNULL(@S+',','')+'MAX(CASE WHEN NUM='+LTRIM(NUM)+' THEN 时间 ELSE '''' END)AS ''时间'+LTRIM(NUM)+''''
FROM (
SELECT *,(SELECT COUNT(1) FROM #T WHERE 姓名=T.姓名 AND C4=T.C4 AND ID<=T.ID) AS NUM FROM #T T
)AS T
EXEC('SELECT 姓名,C4,'+ @S+' FROM (SELECT *,(SELECT COUNT(1) FROM #T WHERE 姓名=T.姓名 AND C4=T.C4 AND ID<=T.ID) AS NUM FROM #T T) AS T GROUP BY 姓名,C4 ')
----------------结果----------------------------
/*
姓名 C4 时间1 时间2 时间3 时间4 时间5 时间6 时间7 时间1 时间2 时间3 时间4 时间1 时间2 时间3 时间4 时间5 时间6
------ ----- ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------
班才荣 C0035 2008-12-12 20:58:00.000 2008-12-13 07:30:00.000 2008-12-13 20:54:00.000 2008-12-14 05:00:00.000 2008-12-16 08:02:00.000 2008-12-16 20:56:00.000 2008-12-17 08:07:00.000 2008-12-12 20:58:00.000 2008-12-13 07:30:00.000 2008-12-13 20:54:00.000 2008-12-14 05:00:00.000 2008-12-12 20:58:00.000 2008-12-13 07:30:00.000 2008-12-13 20:54:00.000 2008-12-14 05:00:00.000 2008-12-16 08:02:00.000 2008-12-16 20:56:00.000
蔡梅 C0384 2009-06-13 18:37:00.000 2009-06-13 17:32:00.000 2009-06-13 17:55:00.000 2009-06-13 13:23:00.000 2009-06-13 12:03:00.000 2009-06-13 07:52:00.000 1900-01-01 00:00:00.000 2009-06-13 18:37:00.000 2009-06-13 17:32:00.000 2009-06-13 17:55:00.000 2009-06-13 13:23:00.000 2009-06-13 18:37:00.000 2009-06-13 17:32:00.000 2009-06-13 17:55:00.000 2009-06-13 13:23:00.000 2009-06-13 12:03:00.000 2009-06-13 07:52:00.000
卜胜强 C0497 2009-06-01 14:34:00.000 2009-06-01 14:34:00.000 2009-06-01 14:34:00.000 2009-06-01 14:12:00.000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 2009-06-01 14:34:00.000 2009-06-01 14:34:00.000 2009-06-01 14:34:00.000 2009-06-01 14:12:00.000 2009-06-01 14:34:00.000 2009-06-01 14:34:00.000 2009-06-01 14:34:00.000 2009-06-01 14:12:00.000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
*/