SQL Server 将多行数据合并成一行（将姓名和性别都相同学生的爱好合并到同一行）

[Backcanhave7]

-- 创建测试用表rows_to_row
create table rows_to_row(
name char(5) not null default '',
gender char(1) not null default 0,
hobby varchar(20) not null default ''
)

-- 向测试表添加数据
insert into rows_to_row
values
('张三','1','aaa'),
('张三','0','sss'),
('张三','1','ddd'),
('张三','1','vvv'),
('张三','0','xxx'),
('李四','1','ggg'),
('李四','1','kkk'),
('李四','0','jjj'),
('李四','0','www');

查询要求：将姓名和性别都相同学生的爱好合并到同一行。上表的查询结果应为：
张三 1 aaa,ddd,vvv
张三 0 sss,xxx
李四 1 ggg,kkk
李四 0 jjj,www

另外再说一句，我的SQL Server的版本是2008R2
请问这种查询要怎么实现?哪位可以指点一下

[文盲老顾]

cross apply + for xml

[二月十六]

SELECT name,
       gender,
       STUFF((   SELECT ',' + hobby
                   FROM rows_to_row
                  WHERE name   = a.name
                    AND gender = a.gender
                 FOR XML PATH('')),
             1,
             1,
             '') AS value
  FROM rows_to_row a
 GROUP BY name,
          gender;

[RINK_1]

SELECT NAME,GENDER,STUFF((SELECT ','+HOBBY FROM rows_to_row WHERE NAME=A.NAME AND GENDER=A.GENDER FOR XML PATH ('')),1,1,'')
FROM rows_to_row A
GROUP BY NAME,GENDER

[Backcanhave7]

引用 2 楼 二月十六 的回复:

SELECT name,
       gender,
       STUFF((   SELECT ',' + hobby
                   FROM rows_to_row
                  WHERE name   = a.name
                    AND gender = a.gender
                 FOR XML PATH('')),
             1,
             1,
             '') AS value
  FROM rows_to_row a
 GROUP BY name,
          gender;

引用 1 楼 文盲老顾 的回复:

cross apply + for xml

感谢这么快速的回复！

[二月十六]

引用 4 楼 Backcanhave7 的回复：

问题解决的话，记得结帖

[Backcanhave7]

引用 2 楼 二月十六 的回复:

SELECT name,
       gender,
       STUFF((   SELECT ',' + hobby
                   FROM rows_to_row
                  WHERE name   = a.name
                    AND gender = a.gender
                 FOR XML PATH('')),
             1,
             1,
             '') AS value
  FROM rows_to_row a
 GROUP BY name,
          gender;

感谢！另外我想问下假如合并的字段里有重复值的话我要怎么去重？
假设原表中再增加一个年龄字段，表中有这样的两条记录：('张三','1','aaa',18),('张三','1','aaa',19)
合并的结果中我只想aaa出现一次应该怎么办？

[Backcanhave7]

引用 3 楼 RINK_1 的回复:

SELECT NAME,GENDER,STUFF((SELECT ','+HOBBY FROM rows_to_row WHERE NAME=A.NAME AND GENDER=A.GENDER FOR XML PATH ('')),1,1,'')
FROM rows_to_row A
GROUP BY NAME,GENDER

感谢！

[Backcanhave7]

引用 5 楼 二月十六 的回复:

引用 4 楼 Backcanhave7 的回复：

问题解决的话，记得结帖

好的

[二月十六]

SELECT name,
       gender,
       STUFF((   SELECT ',' + hobby
                   FROM (SELECT DISTINCT name,gender,hobby FROM rows_to_row)t
                  WHERE name   = a.name
                    AND gender = a.gender
                 FOR XML PATH('')),
             1,
             1,
             '') AS value
  FROM rows_to_row a
 GROUP BY name,
          gender;

[Backcanhave7]

引用 9 楼 二月十六 的回复:

SELECT name,
       gender,
       STUFF((   SELECT ',' + hobby
                   FROM (SELECT DISTINCT name,gender,hobby FROM rows_to_row)t
                  WHERE name   = a.name
                    AND gender = a.gender
                 FOR XML PATH('')),
             1,
             1,
             '') AS value
  FROM rows_to_row a
 GROUP BY name,
          gender;

感谢！