CODEWITHSUNDEEP
t-sqlsorting
Pingpong
Pingpong

Reputation: 8009

Alternative solution to display a table in T-SQL

I have a simple employee table that I want to display in a particular order. I want to find out if there are alternative solutions (or better solution) to achieve the same result. The T-SQL script is shown below:

CREATE TABLE Employee(
 EmployeeID INT IDENTITY(1,1) NOT NULL,
 EmployeeName VARCHAR(255) NULL,
 ManagerID INT NULL,
 EmployeeType VARCHAR(20) NULL
)
GO

INSERT INTO Employee (EmployeeName, ManagerID, EmployeeType)
VALUES ('Brad',5,'Memeber');
INSERT INTO Employee (EmployeeName, ManagerID, EmployeeType)
VALUES ('James',3,'Memeber');
INSERT INTO Employee (EmployeeName, ManagerID, EmployeeType)
VALUES ('Ray',null,'Manager');
INSERT INTO Employee (EmployeeName, ManagerID, EmployeeType)
VALUES ('Tom',8,'Memeber');
INSERT INTO Employee (EmployeeName, ManagerID, EmployeeType)
VALUES ('Neil',8,'Memeber');
INSERT INTO Employee (EmployeeName, ManagerID, EmployeeType)
VALUES ('Rob',5,'Memeber');
INSERT INTO Employee (EmployeeName, ManagerID, EmployeeType)
VALUES ('Paul',5,'Memeber');
INSERT INTO Employee (EmployeeName, ManagerID, EmployeeType)
VALUES ('Tim',null,'Manager');
GO
SELECT e.EmployeeType, e.EmployeeName AS [Team Member],    
  (SELECT e2.EmployeeName FROM Employee AS e2 WHERE e2.EmployeeID = e.ManagerID) AS Manager
FROM Employee AS e 
ORDER BY e.EmployeeType, e.EmployeeID

The rows are ordered by manger first, then employeeID. My concerns is that in my solution, it is sorted by the EmployeeType column. Would it be better to sort it by ManagerId column instead? Because the EmployeeType could be changed in the future, say from Manager to Team Manager, which might cause different result!

Upvotes: 1

Views: 188

Answers (2)

Dalex
Dalex

Reputation: 3625

There are no "universal" solution. In your example, not only Employee type but Manager_id can change too. If you need to get similar results, you should order to hierarchy level. In this case first will be manager set, then employee. If Employee will have another managerId, it will stay at the same level.

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138980

If the criteria for a manager is that column ManangerID is null, you can use a case in the order by to get the managers first.

SELECT e.EmployeeType, e.EmployeeName AS [Team Member],    
  (SELECT e2.EmployeeName FROM Employee AS e2 WHERE e2.EmployeeID = e.ManagerID) AS Manager
FROM Employee AS e 
ORDER BY CASE WHEN E.ManagerID IS NULL THEN 0 ELSE 1 END, e.EmployeeID

If you want to set the sort depending on EmployeeType you can do like this

SELECT e.EmployeeType, e.EmployeeName AS [Team Member],    
  (SELECT e2.EmployeeName FROM Employee AS e2 WHERE e2.EmployeeID = e.ManagerID) AS Manager
FROM Employee AS e 
ORDER BY
  CASE EmployeeType
    WHEN 'Manager' THEN 0
    WHEN 'Memeber' THEN 1
    ELSE 2
  END, e.EmployeeID

Or you can use a table with EmpType's that define the sort order

CREATE TABLE EmpType(EmployeeType VARCHAR(20) PRIMARY KEY, SortOrder INT)
GO
INSERT INTO EmpType VALUES('Manager', 1)
INSERT INTO EmpType VALUES('Memeber', 2)

SELECT e.EmployeeType, e.EmployeeName AS [Team Member],    
  (SELECT e2.EmployeeName FROM Employee AS e2 WHERE e2.EmployeeID = e.ManagerID) AS Manager
FROM Employee AS e 
  LEFT OUTER JOIN EmpType as et
    ON e.EmployeeType = et.EmployeeType
ORDER BY et.SortOrder, e.EmployeeID

Upvotes: 1

Related Questions