SQL Server 多行合并成一行与一行拆分成多行

向右看齐 2023-08-17 15:22 153阅读 0赞

### 多行合并成一行(逗号隔开)： ###

原表结构：

![20190926091739246.PNG][]

合并后的结果：

![20190926091939232.PNG][]

建表及插入数据：

-- 创建测试用表rows_to_row
    create table rows_to_row(
    name char(5) not null default '',
    hobby varchar(20) not null default ''
    )
    
    -- 向测试表添加数据
    insert into rows_to_row 
    values
    ('张三','aaa'),
    ('张三','sss'),
    ('张三','ddd'),
    ('李四','ggg'),
    ('李四','kkk'),
    ('李四','jjj');

实现合并效果的代码：

select  name ,
            hobby = ( stuff((select ',' + hobby from rows_to_row where name = Test.name for xml path('')), 1, 1, '') )
    from rows_to_row as Test 
    group by name

在解析前，我们要知道SQL Server中语句的逻辑执行顺序(与MySQL中的执行顺序类似)，stuff函数和for xml path的用法：

执行顺序可以参考：[https://blog.csdn.net/johnf\_nash/article/details/78887618][https_blog.csdn.net_johnf_nash_article_details_78887618]

stuff函数用法可以参考：[https://blog.csdn.net/hzp666/article/details/82796321][https_blog.csdn.net_hzp666_article_details_82796321]

for xml path的用法可以参考：[https://www.cnblogs.com/wangjingblogs/archive/2012/05/16/2504325.html][https_www.cnblogs.com_wangjingblogs_archive_2012_05_16_2504325.html]

解析：

首先来看主查询，主查询按照name进行分组，随后select从分组结果中依次查询出name值李四和张三，接着将李四和张三依次传入子查询中，我们用‘张三’来替换子查询中的Test.name，然后单独执行替换后的子查询，并查看其执行结果：

select ','+hobby from rows_to_row where name='张三' for xml path('')

![20190926095201924.PNG][]

使用stuff函数替换掉上述查询结果中字段值最开始处的逗号：

select STUFF((select ',' + hobby from rows_to_row where name = '张三' for xml path('')), 1, 1, '')

![20190926095519589.PNG][]

完整查询代码：

select name ,
           hobby = ( stuff((select ',' + hobby from rows_to_row where name = Test.name for xml path('')), 1, 1, '') )
    from rows_to_row as Test 
    group by name

### 一行拆分成多行： ###

原表结构：

![20190926104411558.PNG][]

拆分后的结果：

![20190926104549972.PNG][]

建表及插入数据：

--创建测试表：
    create table apply_test(
    id int not null default 0,
    name varchar(20)
    )
    
    -- --向测试表中添加数据
    insert into apply_test
    values
    (1,'a,b,c,d,e'),
    (2,'f,g,h'),
    (3,'i,j'),
    (4,'k'),
    (5,'l');

实现拆分效果的代码：

select a.id,b.name from
    (select id,name=cast('<v>'+replace(name,',','</v><v>')+'</v>' as xml)
     from apply_test ) as a
    outer apply (select name=T.C.value('.','varchar(20)') from a.name.nodes('v') 
    as T(C)) as b  --其中T是表别名，C指表T中的列

在解析前，我们要知道cast函数的用法，outer apply的用法，XQuery中value函数和nodes函数的用法：

cast函数用法可以参考：[https://blog.csdn.net/qq\_41080850/article/details/100992242][https_blog.csdn.net_qq_41080850_article_details_100992242]

outer apply用法可以参考：[https://blog.csdn.net/qq\_41080850/article/details/101207720][https_blog.csdn.net_qq_41080850_article_details_101207720]

XQuery中value函数和nodes函数的用法可以参考：[https://blog.csdn.net/qq\_41080850/article/details/101292703][https_blog.csdn.net_qq_41080850_article_details_101292703]

解析：

其中，

--将apply_test中的name列由varchar型转化成xml型
    select id,name=cast('<v>'+replace(name,',','</v><v>')+'</v>' as xml) from apply_test

查询结果为：

![20190926130341614.PNG][]

select name=T.C.value('.','varchar(20)') from a.name.nodes('v') as T(C)

的作用是将已经转化成xml类型的name列中的值拆分成多个varchar类型的字母。其中，nodes函数负责将name中的每一个xml值都拆分成多个稍短的xml值，nodes('v')中的'v'是路径表达式；value('.','varchar(20)')中的'.'指定拆分点是当前节点'v'，'varchar(20)'表示把拆分后的多个xml值转化成最大长度为20的varchar型的字符串。

为便于理解，举一个例子：

declare @x xml
    
    set @x='<v>a</v><v>b</v><v>c</v><v>d</v><v>e</v>'
    
    --查询一：
    select T.c.query('.') as result1
    FROM @x.nodes('v') T(c)
    
    --查询二：
    select name=T.c.value('.','varchar(20)')
    from @x.nodes('v') T(c)

查询一结果：

![2019092613444372.PNG][]

查询二结果：

![20190926134518200.PNG][]

此外，当outer apply后不跟筛选条件时，a outer apply b的结果就是a和b的笛卡尔积。

所以完整的查询代码为：

select a.id,b.name from
    (select id,name=cast('<v>'+replace(name,',','</v><v>')+'</v>' as xml) from apply_test ) as a
    outer apply (select name=T.C.value('.','varchar(20)') from a.name.nodes('v') as T(C)) as b

其他参考：

[https://blog.csdn.net/qq\_39774060/article/details/80618094][https_blog.csdn.net_qq_39774060_article_details_80618094]

[https://blog.csdn.net/Wikey\_Zhang/article/details/77480118][https_blog.csdn.net_Wikey_Zhang_article_details_77480118]

[20190926091739246.PNG]: /images/20230810/26d9a697ef3d4e53a8a06094d03d980d.png
[20190926091939232.PNG]: /images/20230810/0f750600f7c4468291e0de233112e4c0.png
[https_blog.csdn.net_johnf_nash_article_details_78887618]: https://blog.csdn.net/johnf_nash/article/details/78887618
[https_blog.csdn.net_hzp666_article_details_82796321]: https://blog.csdn.net/hzp666/article/details/82796321
[https_www.cnblogs.com_wangjingblogs_archive_2012_05_16_2504325.html]: https://www.cnblogs.com/wangjingblogs/archive/2012/05/16/2504325.html
[20190926095201924.PNG]: /images/20230810/fd1adafdd9e24de18fd0c5222a84e76f.png
[20190926095519589.PNG]: /images/20230810/66bc7ae8eacc46b0982bc9ca5e07e1ab.png
[20190926104411558.PNG]: /images/20230810/5c9af2fd399c44eebbb3169f7768ef64.png
[20190926104549972.PNG]: /images/20230810/25bab281f0ef40b0961484b788d07833.png
[https_blog.csdn.net_qq_41080850_article_details_100992242]: https://blog.csdn.net/qq_41080850/article/details/100992242
[https_blog.csdn.net_qq_41080850_article_details_101207720]: https://blog.csdn.net/qq_41080850/article/details/101207720
[https_blog.csdn.net_qq_41080850_article_details_101292703]: https://blog.csdn.net/qq_41080850/article/details/101292703
[20190926130341614.PNG]: /images/20230810/e5cffdf604964a599425ea76b4398c84.png
[2019092613444372.PNG]: /images/20230810/00ba7189b6924fb9bd1b1c96195e0baf.png
[20190926134518200.PNG]: /images/20230810/19780920476b423093350409c735f3a3.png
[https_blog.csdn.net_qq_39774060_article_details_80618094]: https://blog.csdn.net/qq_39774060/article/details/80618094
[https_blog.csdn.net_Wikey_Zhang_article_details_77480118]: https://blog.csdn.net/Wikey_Zhang/article/details/77480118