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

原帖：http://blog.csdn.net/htl258/archive/2010/04/01/5439807.aspx

 

原贴：

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 行受影响)
*/