SQL SERVER 一行一列根据符号转多列

原创

mob649e816aeef7 2023-08-27 11:10:40 ©著作权

©著作权归作者所有：来自51CTO博客作者mob649e816aeef7的原创作品，请联系作者获取转载授权，否则将追究法律责任

SQL SERVER 一行一列根据符号转多列

引言

在SQL SERVER中，我们经常需要将一行数据按照符号进行分割，然后存储到多个列中。这种操作在数据清洗、数据转换等场景中非常常见。本文将介绍如何使用SQL SERVER实现一行一列根据符号转多列的操作，并提供相关的代码示例。

背景

在实际的应用中，我们经常会遇到一种情况：数据以字符串的形式存储，每个字段之间用某个符号（如逗号、分号等）分隔，而我们需要将这些数据拆分成多列，以便于后续的数据分析或处理。例如，有一个名为"employees"的表，其中有一列"skills"存储了员工的技能，每个技能之间用逗号分隔，我们希望将这些技能拆分成多个列，方便查询和分析。

解决方案

SQL SERVER提供了多种方法来实现一行一列根据符号转多列的操作，下面将介绍两种常用的方法：使用字符串函数和使用临时表。

方法一：使用字符串函数

SQL SERVER提供了一系列的字符串函数来处理字符串，其中包括了用于分割字符串的函数。我们可以使用这些函数来实现一行一列根据符号转多列的操作。

以下是一个示例，假设我们有一个名为"employees"的表，其中有两列，分别是"employee_id"和"skills"。"skills"列存储了员工的技能，每个技能之间用逗号分隔。

SELECT employee_id,
       PARSENAME(REPLACE(skills, ',', '.'), 4) AS skill1,
       PARSENAME(REPLACE(skills, ',', '.'), 3) AS skill2,
       PARSENAME(REPLACE(skills, ',', '.'), 2) AS skill3,
       PARSENAME(REPLACE(skills, ',', '.'), 1) AS skill4
FROM employees

上述代码中，我们使用了REPLACE函数将逗号替换为点号，然后使用PARSENAME函数将字符串拆分成多个列。PARSENAME函数的第二个参数表示需要获取的列的索引，从左往右依次递增。

方法二：使用临时表

另一种常见的方法是使用临时表。我们首先创建一个临时表，然后将字符串拆分成多个行，最后将每一行转换成多个列。

以下是一个示例，假设我们有一个名为"employees"的表，其中有两列，分别是"employee_id"和"skills"。"skills"列存储了员工的技能，每个技能之间用逗号分隔。

-- 创建临时表
CREATE TABLE #temp_skills (
    employee_id INT,
    skill VARCHAR(50)
)

-- 将字符串拆分成多行
INSERT INTO #temp_skills (employee_id, skill)
SELECT employee_id, value
FROM employees
CROSS APPLY STRING_SPLIT(skills, ',')

-- 将每一行转换成多列
SELECT employee_id,
       MAX(CASE WHEN rn = 1 THEN skill END) AS skill1,
       MAX(CASE WHEN rn = 2 THEN skill END) AS skill2,
       MAX(CASE WHEN rn = 3 THEN skill END) AS skill3,
       MAX(CASE WHEN rn = 4 THEN skill END) AS skill4
FROM (
    SELECT employee_id,
           skill,
           ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY (SELECT NULL)) AS rn
    FROM #temp_skills
) AS t
GROUP BY employee_id

上述代码中，我们首先创建了一个名为"#temp_skills"的临时表，用于存储拆分后的技能数据。然后使用STRING_SPLIT函数将字符串拆分成多行，并插入到临时表中。最后，我们使用CASE语句将每一行转换成多个列。