SQL一条数据拆分成多条

有时候在数据库中，有一些数据需要拆分成多条记录，例如表中有一列包含了多个值，我们需要将其每个值分别拆分成一条记录。那么该怎么做呢？本文将介绍两种常见的SQL拆分数据的方法。

方法一：使用游标

使用游标可以一行一行地读取并处理数据。我们可以在每次读取一行记录时，将该行记录拆分成多行并插入到目标表中，以实现将一条数据拆分成多条的目的。

示例：

假设源表employees中有一列hobbies，每个员工可能会有多个爱好，这些爱好都存储在一条记录中，用逗号隔开：

id	name	hobbies
1	John	swimming,tennis
2	Mary	reading,travel
3	Peter	hiking,photography

我们需要将上述表中的数据按照爱好拆分为多条记录，目标表hobby_list的结构如下：

id	name	hobby
1	John	swimming
2	John	tennis
3	Mary	reading
4	Mary	travel
5	Peter	hiking
6	Peter	photography

我们可以使用下面的代码实现该功能：

DECLARE @id INT, @name VARCHAR(50), @hobbies VARCHAR(MAX), @hobby VARCHAR(50)
DECLARE cur CURSOR FOR
SELECT id, name, hobbies FROM employees
OPEN cur
FETCH NEXT FROM cur INTO @id, @name, @hobbies
WHILE @@FETCH_STATUS = 0
BEGIN
  WHILE CHARINDEX(',', @hobbies) > 0
  BEGIN
    SET @hobby = LTRIM(RTRIM(SUBSTRING(@hobbies, 1, CHARINDEX(',', @hobbies)-1)))
    INSERT INTO hobby_list (id, name, hobby) VALUES (@id, @name, @hobby)
    SET @hobbies = SUBSTRING(@hobbies, CHARINDEX(',', @hobbies)+1, LEN(@hobbies)-CHARINDEX(',', @hobbies))
  END
  SET @hobby = LTRIM(RTRIM(@hobbies))
  INSERT INTO hobby_list (id, name, hobby) VALUES (@id, @name, @hobby)
  FETCH NEXT FROM cur INTO @id, @name, @hobbies
END
CLOSE cur
DEALLOCATE cur

以上代码使用一个游标，每次读取一行记录并拆分其中的爱好字段，将每个爱好插入到目标表中。

但是，游标虽然可行，但由于其一行一行地读取数据，因此效率较低，在处理大量数据时可能会导致性能问题。

方法二：使用递归CTE

使用递归CTE也可以实现一条数据的拆分，相比游标方法，递归CTE方法的效率更高。

示例：

接上面的例子，使用递归CTE可以将一条记录拆分成多条记录。实现代码如下：

WITH tmp AS (
  SELECT id, name, CAST('<root><hobby>' + REPLACE(hobbies, ',', '</hobby><hobby>') + '</hobby></root>' AS XML) hobbies_xml
  FROM employees
)
, cte AS (
  SELECT id, name, hobbies_xml.value('/root[1]/hobby[1]', 'VARCHAR(MAX)') hobby, 2 row_num
  FROM tmp
  UNION ALL
  SELECT id, name, hobbies_xml.value('/root[1]/hobby[' + CAST(row_num AS VARCHAR(5)) + ']', 'VARCHAR(MAX)'), row_num + 1
  FROM cte
  WHERE hobbies_xml.exist('/root[1]/hobby[' + CAST(row_num AS VARCHAR(5)) + ']') = 1
)
INSERT INTO hobby_list (id, name, hobby)
SELECT id, name, hobby
FROM cte
ORDER BY id, row_num

以上代码首先将hobbies字段的值转换为XML格式，然后使用递归CTE对XML数据进行拆分，将每个hobby字段的值作为一条记录插入到目标表中。

以上就是两种常见的SQL拆分数据的方法，根据实际情况选择合适的方法以实现数据拆分的目的。