How to combine multiple rows of employee into single row in SQL Server

Question

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

The results should be like below

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                       .

I forgot put a few notes in my initial post.

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) There will be only one row with same benefit plan per EmpNo/Name.

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.

Answer 1

Sample data:

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')

And a simple group clause:

Actual query (if there are historical records), using ROW_NUMBER will let you find latest record for each User and its Benefit:

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

Query using dynamic SQL if there is unknown amount of Benefits. Might not be very efficient:

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

Query (if there are no historical records):

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

Output:

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

Answer 2

PIVOT solution on StartDate field:

DECLARE @tb AS TABLE
(
    EmpNo INT
    ,Name NVARCHAR(25)
    ,Benefit NVARCHAR(25)
    ,StartDate DATE
    ,[Status] NVARCHAR(25)
);

INSERT INTO @tb VALUES (1, 'ABC', 'Medical', '01/01/2014', 'Active');
INSERT INTO @tb VALUES (1, 'ABC', 'Dental', '02/02/2013', 'Inactive');
INSERT INTO @tb VALUES (1, 'ABC', 'Vision', '03/03/2012', 'Active');
INSERT INTO @tb VALUES (2, 'XYZ', 'Medical', '01/01/2014', 'Active');
INSERT INTO @tb VALUES (2, 'XYZ', 'Dental', '02/02/2012', 'Inactive');

SELECT EmpNo
    ,Name
    ,MAX(MedicalStart) AS MedicalStart
    ,MAX(MedStatus) AS MedStatus
    ,MAX(DenStart) AS DenStart
    ,MAX(DenStatus) AS DenStatus
    ,MAX(VisionStart) AS VisionStart
    ,MAX(VisStatus) AS VisStatus
FROM
(
    SELECT EmpNo
        ,Name
        ,[Medical] AS MedicalStart
        ,CASE 
            WHEN [Medical] IS NOT NULL AND [Status] = 'Active' THEN 'Active' 
            WHEN [Medical] IS NOT NULL AND [Status] = 'Inactive' THEN 'Inactive' 
            ELSE NULL END AS MedStatus
        ,[Dental] AS DenStart
        ,CASE 
            WHEN [Dental] IS NOT NULL AND [Status] = 'Active' THEN 'Active' 
            WHEN [Dental] IS NOT NULL AND [Status] = 'Inactive' THEN 'Inactive' 
            ELSE NULL END AS DenStatus
        ,[Vision] AS VisionStart
        ,CASE 
            WHEN [Vision] IS NOT NULL AND [Status] = 'Active' THEN 'Active' 
            WHEN [Vision] IS NOT NULL AND [Status] = 'Inactive' THEN 'Inactive' 
            ELSE NULL END AS VisStatus
        ,[Status]
    FROM @tb
    PIVOT
    (
        MAX(StartDate)
        FOR Benefit IN ([Medical], [Dental], [Vision])
    ) AS pivotTableDate
) AS tb
GROUP BY EmpNo, Name;

You can check this link 'PIVOT on two or more fields in SQL Server' for information about full PIVOT solution.

Answer 3

You can use Outer Apply to do this. I am assuming that one employee can have any number of medical, dental and vision rows. This query will take the latest StartDate for each type.

Select EmpNo, Name, Medical.StartDate MedicalStart, Medical.Status MedStatus, Dental.StartDate DenStart, Dental.Status DenStatus, Vision.StartDate VisionStart, Vision.Status VisStatus
From (Select Distinct EmpNo, Name From TableName) Emp
Outer Apply (Select Top 1 StartDate, Status From TableName Med Where Benefit='Medical' and Med.EmpNo=Emp.EmpNo Order By StartDate Desc) Medical
Outer Apply (Select Top 1 StartDate, Status From TableName Den Where Benefit='Dental' and Den.EmpNo=Emp.EmpNo Order By StartDate Desc) Dental
Outer Apply (Select Top 1 StartDate, Status From TableName Vis Where Benefit='Vision' and Vis.EmpNo=Emp.EmpNo Order By StartDate Desc) Vision

Let me know if this works for you...

Answer 4

I assume that for each employee you have at most 1 row for Medical, at most 1 row for Dental, and at most 1 row for Vision. If so, you can do it like this:

select 

    t.EmpNo, t.Name, 
    tMedical.MedicalStart, tMedical.MedicalStatus

from 
(
    select
        EmpNo, Name
    from 
        TableName
    group by EmpNo, Name
) t

left outer join

(

    select 
        EmpNo, Name, Benefit, 
        min(StartDate) as MedicalStart, 
        min(Status) as MedicalStatus
    from 
        TableName
    where
        Benefit = 'Medical'
    group by EmpNo, Name, Benefit

) tMedical on t.EmpNo = tMedical.EmpNo and t.Name = tMedical.Name

left outer join ... 

Analogically to tMedical, you can add here left joins to tDental and tVision. And that should be it.