A simple SQL row and column conversion
Author:eaglet
In database development, we often encounter the problem of row and column conversion, such as the following question, department, employee and employee type three sheets, we want to count similar list
Department Number department name total formal staff temporary staff dismiss staff
1 A 30 20 10 1
This problem is not a clue, but to straighten out the idea and then look, the essence is a row and column conversion problem. Let me combine this simple example to implement the row and column conversions.
3 Tables below
Copy Code code as follows:
if exists (select * from sysobjects where id = object_id (' Employeetype ') and type = ' u ')
drop table Employeetype
Go
if exists (select * from sysobjects where id = object_id (' Employee ') and type = ' u ')
drop table Employee
Go
if exists (select * from sysobjects where id = object_id (' Department ') and type = ' u ')
drop table Department
Go
CREATE TABLE Department
(
Id int PRIMARY KEY,
Department varchar (10)
)
CREATE TABLE Employee
(
EmployeeId int PRIMARY KEY,
DepartmentID int Foreign Key (departmentid) References Department (Id), DepartmentID,
EmployeeName varchar (10)
)
CREATE TABLE Employeetype
(
EmployeeId int Foreign Key (EmployeeId) References Employee (EmployeeId), EmployeeId,
Employeetype varchar (10)
)
Describe the relationship between department, employee, and employee types.
Inserting test data
Copy Code code as follows:
Insert Department VALUES (1, ' A ');
Insert Department VALUES (2, ' B ');
Insert Employee VALUES (1, 1, ' Bob ');
Insert Employee VALUES (2, 1, ' John ');
Insert Employee VALUES (3, 1, ' may ');
Insert Employee VALUES (4, 2, ' Tom ');
Insert Employee VALUES (5, 2, ' Mark ');
Insert Employee VALUES (6, 2, ' Ken ');
Insert Employeetype VALUES (1, ' formal ');
Insert Employeetype VALUES (2, ' temporary ');
Insert Employeetype VALUES (3, ' formal ');
Insert Employeetype VALUES (4, ' formal ');
Insert Employeetype VALUES (5, ' dismiss ');
Insert Employeetype VALUES (6, ' formal ');
Look at the list of department, employee, and employee types
Department EmployeeName Employeetype
---------- ------------ ------------
A Bob Official
A John Temp
A May official
B Tom is officially
B Mark fired.
B Ken Official
Now we need to output such a list
Department Number department name total formal staff temporary staff dismiss staff
The problem my idea is to first count the total number of employee types in each department
This is simpler, I made it into a view
Copy Code code as follows:
if exists (select * from sysobjects where id = object_id (' Vdepartmentemployeetype ') and type = ' V ')
Drop View Vdepartmentemployeetype
Go
CREATE VIEW Vdepartmentemployeetype
As
Select Department.id, Department.department, Employeetype.employeetype, Count (employeetype.employeetype) Cnt
From Department, Employee, Employeetype where
Department.id = Employee.departmentid and Employee.employeeid = Employeetype.employeeid
Group by Department.id, Department.department, Employeetype.employeetype
Go
Now select * FROM Vdepartmentemployeetype
Id Department Employeetype Cnt
----------- ---------- ------------ -----------
2 B Dismissal 1
1 A Provisional 1
1 A Official 2
2 B Official 2
With this result, we can achieve the required output by the row and column transformation.
The row and column conversions are implemented using case branching statements, as follows:
Copy Code code as follows:
Select Id as ' department number ', Department as ' department name ',
[Formal] = Sum (case when employeetype = ' official ' then Cnt else 0 end),
[Temporary] = Sum (case when employeetype = ' temporary ' then Cnt else 0 end),
[Dismiss] = Sum (case when employeetype = ' dismiss ' then Cnt else 0 end),
[Total] = Sum (case when Employeetype <> "then Cnt else 0 end)
From Vdepartmentemployeetype
GROUP by Id, Department
Look at the results.
Department Number department name formal temporary dismissal total
----------- ---------- ----------- ----------- ----------- -----------
1 A 2 1 0 3
2 B 2 0 1 3
Now there is another question, what if the employee type cannot be encoded? That means we don't know what types of employees are in the process of writing a program. This is indeed a
More difficult problem, but not unresolved, we can solve this problem by splicing SQL. Look at the code below.
Copy Code code as follows:
DECLARE
@s VARCHAR (max)
SELECT @s = isnull (@s + ', ', ') + ' [' + LTrim (employeetype) + '] = ' +
' Sum (case when employeetype = ' "+
Employeetype + ' then Cnt else 0 end '
From (SELECT DISTINCT employeetype from Vdepartmentemployeetype) temp
EXEC (' Select Id as department number, Department as department name, ' + @s +
', [Total]= sum (case when Employeetype <> ' then Cnt else 0-end) ' +
' From Vdepartmentemployeetype GROUP by Id, Department ')
The results of the implementation are as follows:
Department numbering department name dismissal provisional formal total
----------- ---------- ----------- ----------- ----------- -----------
1 A 0 1 2 3
2 B 1 0 2 3
This result is the same as the previous hard-coded results, but we get all the employee types through the program, and the benefit is that if we add an employee type, such as a "contract worker," We don't need to modify the program to get the output we want.
If your database is SQL Server 2005 or more, you can also use the new features SQLSERVER2005 through PIVOT
Copy Code code as follows:
SELECT Id as ' department number ', Department as ' department name ', [formal], [temporary], [dismiss]
From
(SELECT id,department,employeetype,cnt
From Vdepartmentemployeetype) p
PIVOT
SUM (CNT)
For Employeetype in ([formal], [temporary], [dismiss])
) as Unpvt
The results are as follows
Department Number department name official temporary dismissal
----------- ---------- ----------- ----------- -----------
1 A 2 1 NULL
2 B 2 NULL 1
NULL can be cast to 0 by the ISNULL function, where I don't write a specific SQL statement. This function is still good, but the total seems to be not very good in this way. I wonder if there are any good ways for fellow colleagues.
