CODEWITHSUNDEEP
sqlsql-serverexcel
Harambe Attack Helicopter
Harambe Attack Helicopter

Reputation: 780

Merge or remove repeating values in columns using Excel (or SQL)

I have a query where I join the employee table and logs table to show the logs per employee. The problem is I need to remove those duplicating column vales (show the emp details only to first row per employee). Does anyone know how to do it in Excel or SQL Server?

SELECT e.Employee, e.Position, e.Dept, l.Actioner, s.Status, l.ActionDate
FROM employee e
LEFT JOIN logs l ON e.EmpNo = l.EmpNo
INNER JOIN Status s ON l.StatusId = s.Id
ORDER BY l.Employee, l.ActionDate

Original Output

Employee    |Position   |Dept   |Actioner   |Action     |ActionDate
emp1        |Manager    |IT     |emp1       |Submit     |01/01/2017
emp1        |Manager    |IT     |emp2       |Verify     |01/02/2017
emp1        |Manager    |IT     |emp3       |Approve    |01/03/2017
emp2        |Supervisor |HR     |emp2       |Submit     |01/12/2017
emp2        |Supervisor |HR     |emp3       |Verify     |01/13/2017
emp2        |Supervisor |HR     |emp4       |Approve    |01/14/2017

Desired Output

Employee    |Position   |Dept   |Actioner   |Action     |ActionDate
emp1        |Manager    |IT     |emp1       |Submit     |01/01/2017
                                |emp2       |Verify     |01/02/2017
                                |emp3       |Approve    |01/03/2017
emp2        |Supervisor |HR     |emp2       |Submit     |01/12/2017
                                |emp3       |Verify     |01/13/2017
                                |emp4       |Approve    |01/14/2017

NOTE: I prefer if this can be resolved using Excel function. Thank you!

Upvotes: 2

Views: 65

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82504

Here is one way to do it in SQL Server before 2012 (when the LAG function was introduced).

Using a common table expression with ROW_NUMBER, and a CASE expression to return only the first Employee, Position and Dept: 

;WITH CTE AS
(
    SELECT  e.Employee, 
            e.Position, 
            e.Dept, 
            l.Actioner, 
            s.Status, 
            l.ActionDate,
            ROW_NUMBER() OVER(PARTITION BY e.Employee ORDER BY l.Employee, l.ActionDate) As rn
    FROM employee e
    LEFT JOIN logs l ON e.EmpNo = l.EmpNo
    INNER JOIN Status s ON l.StatusId = s.Id
)

SELECT  CASE WHEN rn = 1 THEN e.Employee END As Employee, 
        CASE WHEN rn = 1 THEN e.Position END As Position, 
        CASE WHEN rn = 1 THEN e.Dept END As Dept, 
        l.Actioner, 
        s.Status, 
        l.ActionDate
FROM CTE
ORDER BY l.Employee, l.ActionDate

Upvotes: 1

Related Questions