Background: when creating a project, we often encounter a table structure that stores a column in the master table separated by commas. For example, when an employee belongs to multiple departments, a project belongs to multiple cities, and a device belongs to multiple projects, many people will add a deptIds VARCHAR (1000) column to the employee table (this article uses multiple departments affiliated to the employee as an example) to save the Department number list (Obviously this does not conform to the first paradigm, but many people have designed it like this. In this article, we will not discuss the Right and Wrong design in this application scenario. If you are interested, you can talk about it in the reply ), then we need to see which departments the employee belongs to in the query list.
Initialize data:
Department table and employee table data:
Copy codeThe Code is as follows:
If exists (SELECT * FROM sys. objects WHERE object_id = OBJECT_ID (n' [dbo]. [Department] ')
Drop table [dbo]. Department
GO
-- Department table
Create table Department
(
Id int,
Name nvarchar (50)
)
Insert into Department (id, name)
SELECT 1, 'personnel authorization'
UNION
SELECT 2, 'engineering shell'
UNION
SELECT 3, 'authorization'
SELECT * FROM Department
If exists (SELECT * FROM sys. objects WHERE object_id = OBJECT_ID (n' [dbo]. [Employee] ')
Drop table [dbo]. Employee
GO
-- Employee table
Create table Employee
(
Id int,
Name nvarchar (20 ),
DeptIds varchar (1000)
)
Insert into Employee (id, name, deptIds)
SELECT 1, 'jiang Dahua ', '1, 2, 3'
UNION
SELECT 2, 'xiaoming ', '1'
UNION
SELECT 3, 'hua ',''
SELECT * FROM Employee
Expected results:
Solution:
The first step is to obtain the following data. The employee table set is connected to the relevant department set. The fun_SplitIds function is used to split ids into ids. Then, the employee set is connected to the obtained set.
Copy codeThe Code is as follows:
Select e. *, ISNULL (D. name, '') AS deptName
FROM Employee AS E
Outer apply dbo. fun_SplitIds (E. deptIds) AS DID
Left join Department as d on did. ID = D. id;
Step 2: You have obtained the above data, and then you need to group the data by ID and perform the aggregation operation on the deptName column, however, SQL SERVER does not provide string aggregation operations. However, when we process tree data, we use CTE as the relational data to make the data with a tree format. In this way, we can also convert this problem into a tree format, the Code is as follows:
Copy codeThe Code is as follows:
; WITH EmployeT (
-- Basic information of the employee (use outer apply to split multiple IDs and associate them with the department table)
-- At this time, IDS stored in the employee table are associated with departments respectively. Next we need to aggregate the deptName in this set into a record.
Select e. *, ISNULL (D. name, '') AS deptName
FROM Employee AS E
Outer apply dbo. fun_SplitIds (E. deptIds) AS DID
Left join Department as d on did. ID = D. id
), Mike (
SELECT id, name, deptIds, deptName
, ROW_NUMBER () OVER (partition by id order by id) AS level_num
FROM EmployeT
), Mike2 (
SELECT id, name, deptIds, CAST (deptName as nvarchar (100) AS deptName, level_num
FROM mike
WHERE level_num = 1
UNION ALL
SELECT m. id, m. name, m. deptIds, CAST (m2.deptName + ',' + m. deptName as nvarchar (100) AS deptName, m. level_num
FROM mike AS m
Inner join mike2 AS m2 ON m. ID = m2.id AND m. level_num = m2.level _ num + 1
), MaxMikeByIDT (
SELECT id, MAX (level_num) AS level_num
FROM mike2
GROUP BY ID
)
Select a. id, A. name, A. deptIds, A. deptName
FROM mike2 AS
Inner join maxMikeByIDT as B on a. id = B. id and a. level_num = B. level_num
Order by a. id OPTION (MAXRECURSION 0)
The result is as follows:
All SQL:
Copy codeThe Code is as follows:
If exists (SELECT * FROM sys. objects WHERE object_id = OBJECT_ID (n' [dbo]. [Department] ')
Drop table [dbo]. Department
GO
-- Department table
Create table Department
(
Id int,
Name nvarchar (50)
)
Insert into Department (id, name)
SELECT 1, 'personnel authorization'
UNION
SELECT 2, 'engineering shell'
UNION
SELECT 3, 'authorization'
SELECT * FROM Department
If exists (SELECT * FROM sys. objects WHERE object_id = OBJECT_ID (n' [dbo]. [Employee] ')
Drop table [dbo]. Employee
GO
-- Employee table
Create table Employee
(
Id int,
Name nvarchar (20 ),
DeptIds varchar (1000)
)
Insert into Employee (id, name, deptIds)
SELECT 1, 'jiang Dahua ', '1, 2, 3'
UNION
SELECT 2, 'xiaoming ', '1'
UNION
SELECT 3, 'hua ',''
SELECT * FROM Employee
-- Create a table value function to split a comma-separated numeric string and return a table with only one column of numbers.
If exists (SELECT * FROM sys. objects WHERE object_id = OBJECT_ID (n' [dbo]. [fun_SplitIds] ')
Drop function [dbo]. fun_SplitIds
GO
Create function dbo. fun_SplitIds (
@ Ids nvarchar (1000)
)
RETURNS @ t_id TABLE (id VARCHAR (36 ))
AS
BEGIN
DECLARE @ I INT, @ j INT, @ l INT, @ v VARCHAR (36 );
SET @ I = 0;
SET @ j = 0;
SET @ l = len (@ Ids );
While (@ j <@ l)
Begin
SET @ j = charindex (',', @ Ids, @ I + 1 );
IF (@ j = 0) set @ j = @ l + 1;
SET @ v = cast (SUBSTRING (@ Ids, @ I + 1, @ j-@ i-1) as VARCHAR (36 ));
Insert into @ t_id VALUES (@ v)
SET @ I = @ j;
END
RETURN;
END
GO
; WITH EmployeT (
-- Basic information of the employee (use outer apply to split multiple IDs and associate them with the department table)
-- At this time, IDS stored in the employee table are associated with departments respectively. Next we need to aggregate the deptName in this set into a record.
Select e. *, ISNULL (D. name, '') AS deptName
FROM Employee AS E
Outer apply dbo. fun_SplitIds (E. deptIds) AS DID
Left join Department as d on did. ID = D. id
), Mike (
SELECT id, name, deptIds, deptName
, ROW_NUMBER () OVER (partition by id order by id) AS level_num
FROM EmployeT
), Mike2 (
SELECT id, name, deptIds, CAST (deptName as nvarchar (100) AS deptName, level_num
FROM mike
WHERE level_num = 1
UNION ALL
SELECT m. id, m. name, m. deptIds, CAST (m2.deptName + ',' + m. deptName as nvarchar (100) AS deptName, m. level_num
FROM mike AS m
Inner join mike2 AS m2 ON m. ID = m2.id AND m. level_num = m2.level _ num + 1
), MaxMikeByIDT (
SELECT id, MAX (level_num) AS level_num
FROM mike2
GROUP BY ID
)
Select a. id, A. name, A. deptIds, A. deptName
FROM mike2 AS
Inner join maxMikeByIDT as B on a. id = B. id and a. level_num = B. level_num
Order by a. id OPTION (MAXRECURSION 0)
