A simple SQL row-column conversion statement

A simple SQL row-column Conversion
Author: eaglet
In database development, we often encounter the problem of column/column conversion, such as the following problems, three tables for departments, employees, and employees. We need to count a list similar to this.
Department No. Department name total formal employee temporary employee dismissed employee
1 A 30 20 10 1
This problem cannot be found at all, but after you straighten out your ideas, it is essentially a matter of column and column conversion. Next I will use this simple example to implement the conversion of rows and columns.
The following three tables Copy codeThe Code is as follows: if exists (select * from sysobjects where id = object_id ('ployeetype ') and type = 'U ')
Drop table EmployeeType
GO
If exists (select * from sysobjects where id = object_id ('employe') 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,
Specified mentid int Foreign Key (specified mentid) References Department (Id), -- specified mentid,
EmployeeName varchar (10)
)
Create table EmployeeType
(
EmployeeId int Foreign Key (EmployeeId) References Employee (EmployeeId), -- EmployeeId,
EmployeeType varchar (10)
)

Describes the relationships between departments, employees, and employee types.
Insert Test DataCopy codeThe Code is 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, 'offset ');
Insert EmployeeType values (6, 'formal ');

Let's take a look at the list of departments, employees, and employee types.
Department EmployeeName EmployeeType
----------------------------------
A Bob officially
A John temporary
A May official
B Tom officially
B Mark dismissed
B Ken formal
Now we need to output such a list
Department No. Department name total formal employee temporary employee dismissed employee
My idea is to first count the total number of employees in each department.
This is relatively simple. I made it into a view.Copy codeThe Code is as follows: if exists (select * from sysobjects where id = object_id ('v1_mentemployeetype ') 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. Dimension mentid and Employee. EmployeeId = EmployeeType. EmployeeId
Group by Department. Id, Department. Department, EmployeeType. EmployeeType
GO

Select * from v1_mentemployeetype
Id Department EmployeeType Cnt
--------------------------------------------
2 B dismissed 1
1 A temporary 1
1 A formal 2
2 B Formal 2
With this result, we can achieve the required output by converting the rows and columns.
The case Branch statement is used for row and column conversion:Copy codeThe Code is as follows: select Id as 'department number', Department as 'department name ',
[Official] = Sum (case when EmployeeType = 'official 'then Cnt else 0 end ),
[Temporary] = Sum (case when EmployeeType = 'temporary 'then Cnt else 0 end ),
[Dismissed] = Sum (case when EmployeeType = 'dismissed 'then Cnt else 0 end ),
[Total] = Sum (case when EmployeeType <> ''then Cnt else 0 end)
From VDepartmentEmployeeType
Group by Id, Department

Check the result
Department No. Department name temporary dismissal total
-----------------------------------------------------------------
1 A 2 1 0 3
2 B 2 0 1 3
There is another problem. What if the employee type cannot be encoded? That is to say, we do not know the types of employees when writing programs. This is indeed
This is a tough problem, but it cannot be solved. We can solve this problem by concatenating SQL statements. See the following code:Copy codeThe Code is 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 v1_mentemployeetype) temp
EXEC ('select Id as Department number, Department as Department name, '+ @ s +
', [Total] = Sum (case when EmployeeType <> ''' then Cnt else 0 end)' +
'From v1_mentemployeetype group by Id, Department ')

The execution result is as follows:
Department No. Department name dismissal Temporary Total
-----------------------------------------------------------------
1 A 0 1 2 3
2 B 1 0 2 3
This result is the same as the previous hard-coded result, but we get all employee types through the program. The advantage of this is that if we add an employee type, for example, we can get the output we want without modifying the program.

If your database is SQL Server 2005 or later, you can also use the new feature "PIVOT" passed by SQLSERVER2005.Copy codeThe Code is as follows: SELECT Id as 'department number', Department as 'department name', [Formal], [temporary], [dismissed]
FROM
(SELECT Id, Department, EmployeeType, Cnt
FROM VDepartmentEmployeeType) p
Bytes
(SUM (Cnt)
FOR EmployeeType IN ([official], [temporary], [Dismissal])
) AS unpvt

The result is as follows:
Department No., Department name, formal temporary dismissal
------------------------------------------------------
1 A 2 1 NULL
2 B 2 NULL 1
The ISNULL function can be used to forcibly convert NULL to 0. Here I will not write the specific SQL statement. This function is good, but it seems that this method is not very good. I don't know if you can find any good solutions.