示例数据:

CREATE TABLE #Test
(
    EmpNo INT
    , Name VARCHAR(255)
    , Benefit VARCHAR(255)
    , StartDate DATETIME2
    , Status VARCHAR(255)
);
INSERT INTO #Test
    (EmpNo, Name, Benefit, StartDate, Status)
VALUES
    (0001, 'ABC', 'Medical', '01/01/2014', 'Active')
    , (0001, 'ABC', 'Dental', '02/02/2013', 'Inactive')
    , (0001, 'ABC', 'Vision', '03/03/2012', 'Active')
    , (0002, 'XYZ', 'Medical', '01/01/2014', 'Active')
    , (0002, 'XYZ', 'Dental', '02/02/2008', 'Inactive')

还有一个简单的group子句:

And a simple group clause:

实际查询(如果有历史记录)，使用ROW_NUMBER将使您找到每个用户及其利益的最新记录:

SELECT T.EmpNo
    , T.Name
    , MAX(CASE WHEN T.Benefit = 'Medical ' THEN CONVERT(VARCHAR(10), CONVERT(DATE, T.StartDate, 106), 103) END) AS MedStart
    , MAX(CASE WHEN T.Benefit = 'Medical' THEN T.Status END) AS MedStatus
    , MAX(CASE WHEN T.Benefit = 'Dental ' THEN CONVERT(VARCHAR(10), CONVERT(DATE, T.StartDate, 106), 103) END) AS DenStart
    , MAX(CASE WHEN T.Benefit = 'Dental' THEN T.Status END) AS DenStatus
    , MAX(CASE WHEN T.Benefit = 'Vision ' THEN CONVERT(VARCHAR(10), CONVERT(DATE, T.StartDate, 106), 103) END) AS VisStart
    , MAX(CASE WHEN T.Benefit = 'Vision' THEN T.Status END) AS VisStatus
FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY EmpNo, Name, Benefit ORDER BY StartDate DESC) AS RowNo
        , EmpNo
        , Benefit
        , Name
        , StartDate
        , Status
    FROM #Test
) AS T
WHERE T.RowNo = 1
GROUP BY T.EmpNo
    , T.Name

使用动态SQL查询是否有未知数量的权益.可能效率不高:

DECLARE @SQL NVARCHAR(MAX) = 'SELECT T.EmpNo, T.Name'
    , @Benefit VARCHAR(MAX);

SELECT @SQL += ', MAX(CASE WHEN T.Benefit = ''' + Benefit + ''' THEN CONVERT(VARCHAR(10), CONVERT(DATE, T.StartDate, 106), 103) END) AS ' + LEFT(Benefit, 3) + 'Star
                , MAX(CASE WHEN T.Benefit = ''' + Benefit + ''' THEN T.Status END) AS ' + LEFT(Benefit, 3) + 'Status'
FROM (SELECT DISTINCT Benefit FROM #Test) AS T

SET @SQL += '
FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY EmpNo, Name, Benefit ORDER BY StartDate DESC) AS RowNo, EmpNo, Benefit, NAME, StartDate, STATUS
    FROM #Test
    ) AS T
WHERE T.RowNo = 1
GROUP BY T.EmpNo, T.Name'

EXEC sp_executesql @SQL

查询(如果没有历史记录):

SELECT T.EmpNo
    , T.Name
    , MAX(CASE WHEN T.Benefit = 'Medical ' THEN CONVERT(VARCHAR(10), CONVERT(DATE, T.StartDate, 106), 103) END) AS MedStart
    , MAX(CASE WHEN T.Benefit = 'Medical' THEN T.Status END) AS MedStatus
    , MAX(CASE WHEN T.Benefit = 'Dental ' THEN CONVERT(VARCHAR(10), CONVERT(DATE, T.StartDate, 106), 103) END) AS DenStart
    , MAX(CASE WHEN T.Benefit = 'Dental' THEN T.Status END) AS DenStatus
    , MAX(CASE WHEN T.Benefit = 'Vision ' THEN CONVERT(VARCHAR(10), CONVERT(DATE, T.StartDate, 106), 103) END) AS VisStart
    , MAX(CASE WHEN T.Benefit = 'Vision' THEN T.Status END) AS VisStatus
FROM #Test AS T
GROUP BY T.EmpNo
    , T.Name

输出:

EmpNo   Name    MedStart    MedStatus   DenStart    DenStatus   VisStart    VisStatus
-------------------------------------------------------------------------------------
1       ABC     01/01/2014  Active      02/02/2013  Inactive    03/03/2012  Active
2       XYZ     01/01/2014  Active      02/02/2008  Inactive    NULL        NULL