MSSQL 多行合并为一行

 select ptl_a01, ptl_piitem,
 
 (SELECT COUNT(ptl_id) FROM dbo.PRODUCT_TRACEABILITY_LOG WHERE ptl_a01=t.ptl_a01 AND ptl_piitem=t.ptl_piitem and (ptl_time>='2016-08-21 00:00:00' and ptl_time<='2016-08-30 23:59:59' ) and ptl_order_type=10 and ptl_poid=20158 ) as ptl_count, 
 
 STUFF((SELECT ','+ltrim(ptl_pdid)  
                    FROM PRODUCT_TRACEABILITY_LOG    
                    WHERE ptl_a01=t.ptl_a01 AND ptl_piitem=t.ptl_piitem and (ptl_time>='2016-08-21 00:00:00' and ptl_time<='2016-08-30 23:59:59' ) and ptl_order_type=10 and ptl_poid=20158 FOR XML PATH('')), 1, 1, '')   
    AS ptl_all_pdid 
 
 FROM PRODUCT_TRACEABILITY_LOG AS t where (ptl_time>='2016-08-21 00:00:00' and ptl_time<='2016-08-30 23:59:59' ) and ptl_order_type=10 and ptl_poid=20158 GROUP BY t.ptl_a01,t.ptl_piitem ORDER BY t.ptl_a01

 

关键字:STUFF 

1、作用

删除指定长度的字符，并在指定的起点处插入另一组字符。

2、语法

STUFF ( character_expression , start , length ,character_expression )

3、示例

以下示例在第一个字符串 abcdef 中删除从第 2 个位置（字符 b）开始的三个字符，然后在删除的起始位置插入第二个字符串，从而创建并返回一个字符串

SELECT STUFF('abcdef', 2, 3, 'ijklmn')
GO

下面是结果集

aijklmnef