1、创建表及插入数据
Create table tb
(
Name varchar(20),
No varchar(20),
Type int
)
Go
Insert Into tb
Select '张三','123456789',1
union
Select '李四','867223',0
union
Select '王五','1232432232',1
2、按分组以逗号关联每组中的数据
SELECT Name,
stuff(
(SELECT ','+No
FROM tb tb2
WHERE type=1
AND tb2.Name=tb1.Name
FOR xml path('')),1,1,'') 座机,
stuff(
(SELECT ','+No
FROM tb tb2
WHERE type=2
AND tb2.Name=tb1.Name
FOR xml path('')),1,1,'') 手机
FROM tb tb1
GROUP BY tb1.Name