mssql sqlserver for xml path 应用举例


摘要:
下文通过实际业务,举例分析for xml path用法,如下所示:
实验环境:sql server 2008 R2



例:
一张维修项目表(维修单号,维修项目),现在我们需要将每张维修单上的维修项目明细汇总为一行数据

 
create table test
(billNo varchar(60),
weiXiuInfo nvarchar(100)
)

insert into test
(billNo,weiXiuInfo)values
('WX01','清洗'),
('WX01','拆'),
('WX01','装'),
('WX02','美容'),
('WX02','测试') 


 
SELECT b.billNo,
    LEFT(weiXiuInfo,LEN(weiXiuInfo)-1) as weiXiuInfo FROM (
    SELECT billNo,
          (SELECT weiXiuInfo+',' FROM test 
                WHERE billNo=a.billNo 
                FOR XML PATH('')) AS weiXiuInfo
       FROM test a 
       GROUP BY billNo
) b 

go
truncate table test
drop     table test 
mssql_sqlserver_for_xml_path应用举例说明

mssql_sqlserver_for_xml_path应用举例说明