SqlServer如何将查询到的多行数据生成为一行

首先添加测试表和数据，Book表中的booktype存的是对应字典表中的id。

--创建测试表Book
CREATE TABLE Book (
name VARCHAR(100),
booktype VARCHAR(100)
);

--插入测试数据
INSERT INTO Book VALUES ('水浒传', '1,2,3');
INSERT INTO Book VALUES ('三体', '1,4');
INSERT INTO Book VALUES ('斗破苍穹', '3,4');

--创建测试表Dictionary
CREATE TABLE Dictionary(
id int,
value VARCHAR(100)
);

--插入测试数据
INSERT INTO Dictionary VALUES (1, '名著');
INSERT INTO Dictionary VALUES (2, '历史');
INSERT INTO Dictionary VALUES (3, '武侠');
INSERT INTO Dictionary VALUES (4, '玄幻');

Book表：

Dictionary(字典)表：

最终我想要的样子是这样：

遍历表生成为一行数据可以巧用 for xml path 方法，一开始我是这样写的，结果sql语句报错了。

select name,booktype,(select value+',' from Dictionary where id in(booktype) for xml path('')) as BookTypeStr from Book;

报错原因是booktype为VARCHAR类型，id为int类型，sql中的“ id in(booktype) ”这一点就会出现类型转换出错，所以要记得in()方法不能这样使用。

更改方法后的sql如下：

select name,booktype,(select value+',' from Dictionary where CHARINDEX( ','+rtrim(id)+',',','+booktype+',')>0 for xml path('')) as BookTypeStr from Book;

可以用CHARINDEX方法来查询id被包含于对应booktype中的所有数据，再用for xml path(’’)方法来生成为一行。注意：id需要加上rtrim()或者是ltrim()方法，来使id转为字符类型，这样才能符合CHARINDEX()的参数类型，否则会报错。
这样就能达到想要的效果了。

发现BookTypeStr中后面多了一个逗号，可以使用LEFT()函数，或者STUFF()函数来截取一下。最终sql如下：

select a.name,a.booktype,left(a.BookTypeStr,LEN(a.BookTypeStr)-1) as BookTypeStr from (
select name,booktype,(select value+',' from Dictionary where CHARINDEX( ','+rtrim(id)+',',','+booktype+',')>0 for xml path('')) as BookTypeStr from Book
)a

效果图为：

把工作中遇到的问题记录下来，方便自己查看的同时，希望也能帮助到大家！
2018/12/25