Store multiple IDs in one column (convert multiple IDs separated by commas into names separated by commas)

Source: Internet
Author: User
During project creation, we often encounter such a table structure that stores a column in the master table separated by commas (,).

During project creation, we often encounter such a table structure that stores a column in the master table separated by commas (,).

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:
The 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.
The 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:
The 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:
The 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)
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.