复杂的行列转换（列转置），加判断条件为输出状态

原贴：

http://topic.csdn.net/u/20100330/07/74b9f2eb-d20f-45c1-b197-a39e7a47fe7f.html?seed=86569699&r=64344385#r_64344385

 

这是一个证书考试记录系统，每个学生都要通过考试获得证书，每个证书都有失效日期。
1,如果学生目前所有证书(3个)都在有效期内（当前系统时间）则显示状态为’正常‘；
2,如果有证书失效，但是此学生参加了新的课程（且早于当前系统时间），则状态显示为'正常'；
3，如果有证书失效，且没有新课程，则显示学生状态'失效'.

当前系统时间 1/21/2010

数据集：

学生ID 学生姓名 证书名称 上次考证时间 失效日期 新课时间
SID Sname Qulification LastDate DueDate NewClass

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
0342S aa CCDF 1/23/2010 1/19/2010 2/1/2010
0748T bb RTDF 1/22/2010 5/22/2010 2/1/2010
0748T bb CCDF Null 5/22/2010 2/1/2010
0748T bb FDO Null 5/22/2010 2/1/2010
55576 cc CCDF 4/22/2008 4/29/2010 2/1/2010
55576 cc FDO 4/22/2008 4/29/2010 2/1/2010
55576 cc RTDF 4/22/2008 4/29/2010 2/1/2010
64221 dd RTDF 1/20/2010 5/22/2010 2/1/2010

 

希望输出结果
学生ID 姓名 RTDF上次 RTDF失效 CCDF上次 CCDF失效 FDO上次 FDO失效 状态
SID Sname RTDF_Comp RTDF_Due CCDF_Completed CCDF_Due FDO_Completed FDO_Due Status

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
0342S aa No Class No Class 1/23/2010 1/19/2010 No Class No Class Expire
55576 cc 4/22/2008 4/29/2010 4/22/2008 4/29/2010 4/22/2008 4/29/2010 Current
0748T bb 1/22/2010 5/22/2010 New Class 5/22/2010 New Class 5/22/2010 Current
64221 dd 1/20/2010 5/22/2010 No Class No Class No Class No Class Expire

 

 

代码处理过程：

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-01 07:19:04
--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
--          Jul  9 2008 14:43:34
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tb

IF NOT OBJECT_ID('[tb]') IS NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb]([SID] NVARCHAR(10),[Sname] NVARCHAR(10),[Qulification] NVARCHAR(10),[LastDate] DATETIME,[DueDate] DATETIME,[NewClass] DATETIME)
INSERT [tb]
SELECT '0342S','aa','CCDF',N'1/23/2010',N'1/19/2010',N'2/1/2010' UNION ALL
SELECT '0748T','bb','RTDF',N'1/22/2010',N'5/22/2010',N'2/1/2010' UNION ALL
SELECT '0748T','bb','CCDF',NULL,N'5/22/2010',N'2/1/2010' UNION ALL
SELECT '0748T','bb','FDO',NULL,N'5/22/2010',N'2/1/2010' UNION ALL
SELECT '55576','cc','CCDF',N'4/22/2008',N'4/29/2010',N'2/1/2010' UNION ALL
SELECT '55576','cc','FDO',N'4/22/2008',N'4/29/2010',N'2/1/2010' UNION ALL
SELECT '55576','cc','RTDF',N'4/22/2008',N'4/29/2010',N'2/1/2010' UNION ALL
SELECT '64221','dd','RTDF',N'1/20/2010',N'5/22/2010',N'2/1/2010'
GO
--SELECT * FROM [tb]

-->SQL查询如下:

DECLARE @S VARCHAR(8000)
SELECT @S='SELECT SID,[Sname]'
SELECT @S=@S+',MIN(CASE WHEN [Qulification]='''+[Qulification]+''' AND [NewClass] IS NULL THEN ISNULL(CONVERT(VARCHAR,[LastDate],111),''NO CLASS'')
                        WHEN [Qulification]='''+[Qulification]+''' AND [NewClass] IS NOT NULL THEN ISNULL(CONVERT(VARCHAR,[LastDate],111),''NEW CLASS'')
                        ELSE ''No Class'' END)['+[Qulification]+'上次]'+
            +',MIN(CASE WHEN [Qulification]='''+[Qulification]+''' THEN CONVERT(VARCHAR,[DueDate],111) ELSE ''No Class'' END)['+[Qulification]+'失效]'
FROM (SELECT [QULIFICATION] FROM TB GROUP BY CHARINDEX(LEFT([QULIFICATION],1),'RCF'),[QULIFICATION]) T
SELECT @S=@S+',Status=CASE SUM(CASE WHEN [DueDate]>GETDATE() OR ([DueDate]<GETDATE() AND ISNULL([NewClass],0)>GETDATE()) THEN 1 ELSE 0 END) WHEN 3 THEN ''Current'' ELSE ''Expire'' END
            FROM TB --WHERE Sname<>''AA''
            GROUP BY SID,[Sname]'           
EXEC(@S)
/*
SID      Sname  RTDF上次     RTDF失效         CCDF上次       CCDF失效        FDO上次        FDO失效            Status
--------  -------  ----------        ----------          ----------          ----------         ----------          -------------       -----------
0342S    aa   No Class         No Class        2010/01/23    2010/01/19    No Class          No Class        Expire
0748T    bb   2010/01/22    2010/05/22    NEW CLASS    2010/05/22    NEW CLASS    2010/05/22    Current
55576    cc    2008/04/22    2010/04/29    2008/04/22    2010/04/29    2008/04/22    2010/04/29    Current
64221    dd   2010/01/20    2010/05/22    No Class          No Class        No Class         No Class        Expire

(4 行受影响)
*/