如何在SQL Server中将多行员工合并为单行 [英] How to combine multiple rows of employee into single row in 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中将多行员工合并为单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！