合并字符串和FOR XML子句的各种用法

----转载

----------------合并多行记录为一条记录的方法总汇
CREATE TABLE tb(id int, [value] varchar(10))
INSERT tb SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'
select * from tb
SELECT *
FROM (SELECT DISTINCT id FROM tb) A OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM tb N
WHERE id = A.id
FOR XML AUTO
), '<N value="', ','), '"/>', ''), 1, 1, '')
)N

SELECT id, [values]=STUFF((SELECT ','+[value] FROM tb t WHERE id=tb.id FOR XML PATH('')), 1, 1, '')
FROM tb
GROUP BY id
-----------------合并多行记录为一条记录的方法总汇
/
----合并字符串
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO

---->建表
create table [TB]([d] varchar(4),[s] varchar(4),[pp] varchar(6),[mm] varchar(4))
insert [TB]
select '长沙','盘螺','HRB400','10mm' union all
select '长沙','盘螺','HRB400','8mm' union all
select '长沙','盘螺','HRB400','Ф10'
GO

SELECT a.[d],a.[s],a.[pp],
[mm] =stuff((
select ','+convert(varchar(20),[mm]) FROM [TB] WHERE [d] = a.[d]  and  [s] = a.[s] and [pp] = a.[pp] for xml path('')
   ),1,1,'')
FROM [TB] a
group by a.[d],a.[s],a.[pp]

--> 查询结果
SELECT * FROM [TB]
--> 删除表格
--DROP TABLE [TB]

--或者
--create table tb(ad nvarchar(5),proj nvarchar(10), xh nvarchar(10),gg nvarchar(10))
--insert into tb select '长沙','盘螺','HRB400','10mm'
--insert into tb select '长沙','盘螺','HRB400','10mm'
--insert into tb select '长沙','盘螺','HRB400','10mm'
--go
--select ad,proj,xh,stuff((select ','+gg from tb where xh=a.xh for xml path('')),1,1,'') from tb a group by ad,proj,xh

select a.ad,a.proj,
a.xh,gg=stuff((select ','+convert(varchar(128),gg) from tb where ad=a.ad and proj=a.proj and xh=a.xh for xml path('d')),1,1,'')
from tb as a
group by a.ad,a.proj,a.xh

---SQL中FOR XML子句的各种用法
1、AUTO模式：返回数据表为起表名的元素，每一列的值返回为属性；
SELECT *  FROM tb for xml auto
2、RAW模式：返回数据行为元素，每一列的值作为元素的属性；
SELECT *  FROM tb for xml raw('fsf')
3、PATH模式：通过简单的XPath语法来允许用户自定义嵌套的XML结构、元素、属性值
SELECT *  FROM tb for xml path('fsf')
4、EXPLICIT模式：通过SELECT语法定义输出XML的结构