如何在SQL Server中将多行员工合并为单行

问题描述：

我有下表，下面是预期结果.如果可以通过简单的方法在SQL Server中获得预期的结果，请告诉我.

I'm having following table and below is expected results. Please let me known if there is a easy to way to get the expected results in SQL server.

EmpNo Name   Benefit   StartDate     Status
--------------------------------------------
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

结果应如下所示

EmpNo   Name  MedicalStart MedStatus  DenStart  DenStatus VisionStart     VisStatus
---------------------------------------------------------------------------------------
0001    ABC   01/01/2014    Active    02/02/2013    Inactive   03/03/2012    Active
0002    XYZ   01/01/2014    Active    02/02/2008    Inactive                       .

我忘了在最初的帖子中写些笔记.

1)有10个福利计划可用，因此雇员可以注册多达10个的任何数目的计划(全部计划或很少计划或根本没有计划).

1) There are 10 benefit plans available, so an employee may enroll for any number of plans up to ten (all plan or few plan or no plans at all).

2)每个EmpNo/名称只有一个行具有相同的福利计划.

2) There will be only one row with same benefit plan per EmpNo/Name.

3)此外，每行都有几个字段，例如，选举选项(自我"，家庭"等)等等.为了简单起见，我没有将这个问题包括在内.

3) Also, there are several fields associated with each row, for example, election option (Self, Family, etc) and many more. To make it simple, I have not included in the question.

答

示例数据:

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

如何在SQL Server中将多行员工合并为单行

相关推荐