Background: When you do a project, you often encounter a table structure in which a column in the main table holds a comma-separated ID. For example, when an employee is subordinate to more than one department, when a project is subordinate to multiple cities, and when a device is subordinate to multiple projects, many people add a deptids VARCHAR (1000) column to the employee table (for example, the employee is subordinate to multiple departments), To save a list of departmental numbers (obviously this does not conform to the first paradigm, but a lot of people design this, in this article we will not discuss in this scenario, so the design of the right and wrong, interested in the reply to talk about it, and then we in the query list to see where the employee subordinate departments.
Initializing data:
Department table, Employee table data:
Copy Code code 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 Department '
UNION
SELECT 2, ' engineering Department '
UNION
SELECT 3, ' Department of Management '
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, ' Shang ', ' 1,2,3 '
UNION
SELECT 2, ' xiaoming ', ' 1 '
UNION
SELECT 3, ' Xiao Hua ', '
SELECT * from Employee
The desired result:
Workaround:
The first step is to get the following data. Cross-connect the employee table collection with the related Department collection, using the Fun_splitids function (which splits IDs into ID lists), and then the employee collection crosses the resulting collection.
Copy Code code 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;
The second step is to have the data as above, and then do is to group by ID, and the deptname column do aggregation operations, but unfortunately, SQL Server has not yet provided to the operation of the string aggregation. But the idea is that when we deal with tree-structured data, we use a CTE to do relational data and make tree-shaped data, so we can also turn this problem into a tree format problem, the code reads as follows:
Copy Code code as follows:
; With Employet as (
--Basic employee information (use outer apply to split multiple IDs and then associate them with the departmental table)
--The IDs that are stored in the employee table are associated with the department at this time, and the Deptname in this collection is required to synthesize 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 as (
SELECT Id,name,deptids,deptname
, Row_number () over (PARTITION by id) as Level_num
From Employet
), Mike2 as (
SELECT Id,name,deptids,cast (Deptname as NVARCHAR) 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 ()) 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 as (
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 A
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 results are as follows:
All sql:
Copy Code code 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 Department '
UNION
SELECT 2, ' engineering Department '
UNION
SELECT 3, ' Department of Management '
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, ' Shang ', ' 1,2,3 '
UNION
SELECT 2, ' xiaoming ', ' 1 '
UNION
SELECT 3, ' Xiao Hua ', '
SELECT * from Employee
--Create a table-valued function that splits a comma-separated number string and returns 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 as (
--Basic employee information (use outer apply to split multiple IDs and then associate them with the departmental table)
--The IDs that are stored in the employee table are associated with the department at this time, and the Deptname in this collection is required to synthesize 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 as (
SELECT Id,name,deptids,deptname
, Row_number () over (PARTITION by id) as Level_num
From Employet
), Mike2 as (
SELECT Id,name,deptids,cast (Deptname as NVARCHAR) 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 ()) 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 as (
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 A
INNER JOIN Maxmikebyidt as B on A.id=b.id and A.level_num=b.level_num
ORDER by a.ID OPTION (maxrecursion 0)
