您的位置:

SQL 一行拆分成多行

SQL 语言是访问和处理关系型数据的一种标准语言,SQL 一行拆分成多行常见于一些需要用到多行的sql查询。

一、将一行数据转换成多行

在实际的业务中,往往会遇到需要将数据进行拆分成多行的情况。例如,一条记录包括多个联系人,需要将联系人从一行数据拆分成多行。这时候我们可以使用 CROSS APPLY 函数,将文本字符串转换成行集合。

SELECT * 
FROM   (
           SELECT 'John,Male,30' AS Info 
           UNION ALL 
           SELECT 'Kelly,Female,25' AS Info 
           UNION ALL 
           SELECT 'James,Male,35' AS Info 
       ) T 
       CROSS APPLY
       (
           SELECT f.item  
           FROM STRING_SPLIT(t.info, ',') AS f
       ) x 

在上述代码中,CROSS APPLY 函数根据逗号分割将文本串进行转换,变成多行数据。

二、 SQL 拆分多行

此时我们再看一个反向的操作,即将多行数据拆分成单行字符串。在实际业务中,往往会存在一些需要将多行数据转化为字符串的需求,例如生成数据的报表。为此,我们可以使用 STUFFFOR XML PATH函数来完成这个过程。

DECLARE @Data TABLE
(
   Id    INT,
   Name  VARCHAR(50),
   Phone VARCHAR(50)
);

INSERT INTO @Data
VALUES (1, 'John', '111-111-1111'),
       (2, 'Frank', '222-222-2222'),
       (3, 'Kelly', '333-333-3333');

SELECT Id, 
       STUFF((SELECT '; ' + Phone 
              FROM   @Data 
              WHERE  Id = d.Id 
              FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS Phones
FROM   @Data AS d;

在上述代码中,STUFF 函数将多行数据拼接成一行字符串,而 FOR XML PATH 元素将每个值转化为XML元素。

三、 SQL 多行合并成一行

反之,我们也可以将多行数据合并成单行字符串,这时候就需要用到 GROUP_CONCAT 函数。

SELECT NAME, 
GROUP_CONCAT(DISTINCT Phone 
ORDER BY Phone DESC 
SEPARATOR '|') AS Phonelist
FROM   @Data 
GROUP  BY Name;

在上述代码中,GROUP_CONCAT 函数将所有数据行合并成单行,分隔符为 |

四、SQL 逗号分割转成多行

在实际业务中,常常会遇到一些需要将逗号分隔字符串转换成多行数据的需求。这时候,我们可以使用 STRING_SPLIT 函数来完成这个操作:

SELECT value 
FROM   STRING_SPLIT('John,Kelly,James', ',')

这段代码可以将以逗号分隔的字符串拆分成多行。

五、SQL 一行拆分成多行的优化

在实际业务中,为了提高查询性能,我们通常需要对查询进行优化。首先需要确保语句的正确性。其次需要根据数据量的大小和查询频率来选择合适的索引。最后,我们可以使用分批处理(分页)来避免在查询大量数据时产生内存溢出等问题。

例如,在使用 STUFF 函数进行数据拼接时,如果一次拼接的数据过大,那么很容易就会出现内存溢出的问题。这时我们可以使用分批处理,每次只处理一定数量的数据,避免内存压力过大。

DECLARE @Start INT = 1;
DECLARE @End INT = 100;
 
WHILE (@Start <= (SELECT MAX(Id) FROM @Data))
BEGIN
    SELECT Id, STUFF((SELECT ', ' + Phone
                       FROM @Data
                       WHERE Id BETWEEN @Start AND @End
                       FOR XML PATH('')), 1, 1, '') AS Phones
    FROM @Data
    WHERE Id BETWEEN @Start AND @End;
 
    SET @Start = @End + 1;
    SET @End = @End + 100;
END;

在上述代码中,我们将大量数据分割成若干个小批次,以减少每次查询的数据量,提高查询性能。

六、 总结

SQL 一行拆分成多行的应用十分常见,并且在实际业务操作中也十分必要。我们可以通过多种方式来实现这一操作,包括使用 CROSS APPLY 函数、 STUFFFOR XML PATH函数、 GROUP_CONCAT 函数以及 STRING_SPLIT 函数。同时,在查询性能优化上,需要注意语句的正确性、索引选择以及分批处理等因素。