SqlServer实现Oracle的wm_concat()函数功能

  SELECT a.partno,
                   a.num,
                   CASE
                     WHEN Isnull(b.num, '') = '' THEN '0'
                     ELSE b.num
                   END                                                                FA,
                   Cast(b.num * 1.0 / ( a.num + b.num ) AS DECIMAL(18, 10)) * 1000000 dppm,
                   Stuff((SELECT '|' + t.field1 + ':' + Cast( t.num AS NVARCHAR)
                          FROM   (SELECT m.field1,
                                         Count(1) num
                                  FROM   sfcrepairmain m
                                         RIGHT JOIN Sfcrepairpart r
                                                 ON m.sysserialno = r.sysserialno
                                                    AND m.lineseqno = m.lineseqno
                                  WHERE  r.failurepartno = a.partno
                                         AND Isnull(m.field1, '') <> ''
                                  GROUP  BY m.field1) t
                          FOR xml path('')), 1, 1, '')                                details
            FROM   sql1 a
                   LEFT JOIN sql2 b
                          ON a.partno = b.failurepartno